C# 对数据库操作（配合 sqlhelper）

using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;

using Model;using DBUtility;

namespace DAL{    public static partial class SmallClassService    {        // 添加        public static SmallClassInfo InsertSmallClass(SmallClassInfo smallClass)        {            string sql =                "INSERT SmallClass (Sname, SBid, Sinformation, ManufactureDate, Piceturepath)" +                "VALUES (@Sname, @SBid, @Sinformation, @ManufactureDate, @Piceturepath)";            sql += " ; SELECT @@IDENTITY ";

            try            {                SqlParameter[] para = new SqlParameter[]                {                    new SqlParameter("@SBid", smallClass.SB.Bid), // 外键                    new SqlParameter("@Sname", smallClass.Sname),                    new SqlParameter("@Sinformation", smallClass.Sinformation),                    new SqlParameter("@ManufactureDate", smallClass.ManufactureDate),                    new SqlParameter("@Piceturepath", smallClass.Piceturepath)                };                int newId = int.Parse(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, sql, para).ToString());                return GetSmallClassBySid(newId);            }            catch (Exception e)            {                Console.WriteLine(e.Message);                throw e;            }        }        // 删除对象        public static bool DeleteSmallClass(SmallClassInfo smallClass)        {            return DeleteSmallClassBySid( smallClass.Sid );        }

        // 删除，按主键删除        public static bool DeleteSmallClassBySid(int sid)        {            string sql = "DELETE SmallClass WHERE Sid = @Sid";

            try            {                SqlParameter[] para = new SqlParameter[]                {                    new SqlParameter("@Sid", sid)                };                return 1 == SqlHelper.ExecuteNonQuery( SqlHelper.ConnectionString, CommandType.Text, sql, para );            }            catch (Exception e)            {                Console.WriteLine(e.Message);                throw e;            }        }        // 删除，按唯一约束键删除        // 修改        public static bool UpdateSmallClass(SmallClassInfo smallClass)         {            string sql =                "UPDATE SmallClass " +                "SET " +                    "SBid = @SBid, " + // 外键                    "Sname = @Sname, " +                    "Sinformation = @Sinformation, " +                    "ManufactureDate = @ManufactureDate, " +                    "Piceturepath = @Piceturepath " +                "WHERE Sid = @Sid";

            try            {                SqlParameter[] para = new SqlParameter[]                {                    new SqlParameter("@Sid", smallClass.Sid),                    new SqlParameter("@SBid", smallClass.SB.Bid), // 外键                    new SqlParameter("@Sname", smallClass.Sname),                    new SqlParameter("@Sinformation", smallClass.Sinformation),                    new SqlParameter("@ManufactureDate", smallClass.ManufactureDate),                    new SqlParameter("@Piceturepath", smallClass.Piceturepath)                };

                return 1 == SqlHelper.ExecuteNonQuery( SqlHelper.ConnectionString, CommandType.Text, sql, para );            }            catch (Exception e)            {                Console.WriteLine(e.Message);                throw e;            }        }       

        // 获取对象，根据主键获取        public static SmallClassInfo GetSmallClassBySid(int sid)        {            string sql = "SELECT * FROM SmallClass WHERE Sid = @Sid";

            try            {                using (SqlDataReader reader = SqlHelper.ExecuteReader( SqlHelper.ConnectionString, CommandType.Text, sql, new SqlParameter("@Sid", sid)))                {                    if (reader.Read())                    {                        SmallClassInfo smallClass = new SmallClassInfo();                        smallClass.Sid = (int)reader["Sid"];                        smallClass.Sname = (string)reader["Sname"];                        smallClass.SBid = (int)reader["SBid"];                        smallClass.Sinformation = (string)reader["Sinformation"];                        smallClass.ManufactureDate = (string)reader["ManufactureDate"];                        smallClass.Piceturepath = (string)reader["Piceturepath"];                        reader.Close();                        smallClass.SB = BigClassService.GetBigClassByBid(smallClass.SBid);                        return smallClass;                    }                    else                    {                        reader.Close();                        return null;                    }                }            }            catch (Exception e)            {                Console.WriteLine(e.Message);                throw e;            }        }        public static SmallClassInfo GetSmallClassBySname(string sname)        {            string sql = "SELECT * FROM SmallClass WHERE Sname = @Sname";

            try            {                using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, sql, new SqlParameter("@Sname", sname)))                {                    if (reader.Read())                    {                        SmallClassInfo smallClass = new SmallClassInfo();

                        smallClass.Sid = (int)reader["Sid"];                        smallClass.Sname = (string)reader["Sname"];                        smallClass.SBid = (int)reader["SBid"];                        smallClass.Sinformation = (string)reader["Sinformation"];                        smallClass.ManufactureDate = (string)reader["ManufactureDate"];                        smallClass.Piceturepath = (string)reader["Piceturepath"];

                        reader.Close();

                        smallClass.SB = BigClassService.GetBigClassByBid(smallClass.SBid);

                        return smallClass;                    }                    else                    {                        reader.Close();                        return null;                    }                }            }            catch (Exception e)            {                Console.WriteLine(e.Message);                throw e;            }        }        // 获取对象，按唯一约束键获取        // 获取对象集合，所有对象        public static IList<SmallClassInfo> GetAllSmallClasses()        {            string sqlAll = "SELECT * FROM SmallClass";            return GetSmallClassesBySql( sqlAll );        }        // 获取对象集合，根据外键获取        public static IList<SmallClassInfo> GetSmallClassesBySBid (int sBid)        {            string sql = "SELECT * FROM SmallClass WHERE SBid ="+sBid;            return GetSmallClassesBySql( sql,null                //new SqlParameter("@SBid", sBid)                );        }        private static IList<SmallClassInfo> GetSmallClassesBySql( string safeSql )        {            List<SmallClassInfo> list = new List<SmallClassInfo>();

            try            {                DataTable table = SqlHelper.GetDataTable( SqlHelper.ConnectionString, CommandType.Text, safeSql );                foreach (DataRow row in table.Rows)                {                    SmallClassInfo smallClass = new SmallClassInfo();                    smallClass.Sid = (int)row["Sid"];                    smallClass.Sname = (string)row["Sname"];                    smallClass.SBid = (int)row["SBid"];                    smallClass.Sinformation = (string)row["Sinformation"];                    smallClass.ManufactureDate = (string)row["ManufactureDate"];                    smallClass.Piceturepath = (string)row["Piceturepath"];                    smallClass.SB = BigClassService.GetBigClassByBid((int)row["SBid"]); // 外键                    list.Add(smallClass);                }                return list;            }            catch (Exception e)            {                Console.WriteLine(e.Message);                throw e;            }        }        public static IList<SmallClassInfo> GetSmallClassesBySql( string sql, params SqlParameter[] values )        {            List<SmallClassInfo> list = new List<SmallClassInfo>();

            try            {                DataTable table = SqlHelper.GetDataTable( SqlHelper.ConnectionString, CommandType.Text, sql, values );                foreach (DataRow row in table.Rows)                {                    SmallClassInfo smallClass = new SmallClassInfo();                    smallClass.Sid = (int)row["Sid"];                    smallClass.Sname = (string)row["Sname"];                    smallClass.SBid = (int)row["SBid"];                    smallClass.Sinformation = (string)row["Sinformation"];                    smallClass.ManufactureDate = (string)row["ManufactureDate"];                    smallClass.Piceturepath = (string)row["Piceturepath"];                    smallClass.SB = BigClassService.GetBigClassByBid((int)row["SBid"]); // 外键                    list.Add(smallClass);                }                return list;            }            catch (Exception e)            {                Console.WriteLine(e.Message);                throw e;            }        }           }}

转载于:https://www.cnblogs.com/zt04985807/archive/2008/11/29/1343928.html

相关资源：数据结构—成绩单生成器