C# 数据库的基本操作(SQL Server)
C# 数据库的基本操作(SQL Server)
一、首先是与sqlserver的基本操作
连接字符在配置文件中是这样写的
- <connectionStrings>
- <add name="ConnStr" connectionString="data source=xp-ba785745002d;database=databaseonline;Uid=sa;pwd=sasa"/>
- </connectionStrings>
基本操作类如下:
- public class SQLHelper
- {
- private SqlConnection sqlCon = null;
- private SqlCommand cmd = null;
- private SqlDataReader sdr = null;
- public SQLHelper()
- {
- sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
- }
- /// <summary>
- /// 打开数据库连接
- /// </summary>
- /// <returns></returns>
- private SqlConnection GetCon()
- {
- if (sqlCon.State==ConnectionState.Closed)
- {
- sqlCon.Open();
- }
- return sqlCon;
- }
- /// <summary>
- /// 执行不带参数的增删改sql语句或存储过程
- /// </summary>
- /// <param name="cmdText">增删改sql语句或存储过程</param>
- /// <param name="ct">命令类型</param>
- /// <returns></returns>
- public int ExecuteNonQuery(string cmdText, CommandType ct)
- {
- int rex;
- try
- {
- SqlCommand sqlcom = new SqlCommand(cmdText,GetCon());
- sqlcom.CommandType = ct;
- rex =sqlcom.ExecuteNonQuery();
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- if (sqlCon.State==ConnectionState.Open)
- {
- sqlCon.Close();
- }
- }
- return rex;
- }
- /// <summary>
- /// 执行带参数的增删改SQL语句或存储过程
- /// </summary>
- /// <param name="cmdText">增删改SQL语句或存储过程</param>
- /// <param name="ct">命令类型</param>
- /// <returns></returns>
- public int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct)
- {
- int res;
- using (cmd = new SqlCommand(cmdText, GetCon()))
- {
- cmd.CommandType = ct;
- cmd.Parameters.AddRange(paras);
- res = cmd.ExecuteNonQuery();
- }
- return res;
- }
- /// <summary>
- /// 执行带参数的查询SQL语句或存储过程
- /// </summary>
- /// <param name="cmdText">查询SQL语句或存储过程</param>
- /// <param name="paras">参数集合</param>
- /// <param name="ct">命令类型</param>
- /// <returns></returns>
- public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)
- {
- DataTable dt = new DataTable();
- cmd = new SqlCommand(cmdText,GetCon());
- cmd.CommandType = ct;
- cmd.Parameters.AddRange(paras);
- using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
- {
- dt.Load(sdr);
- }
- return dt;
- }
- /// <summary>
- /// 执行不带参数的查询SQL语句或存储过程
- /// </summary>
- /// <param name="cmdText">查询SQL语句或存储过程</param>
- /// <param name="ct">命令类型</param>
- /// <returns></returns>
- public DataTable ExecuteQuery(string cmdText, CommandType ct)
- {
- DataTable dt = new DataTable();
- cmd = new SqlCommand(cmdText, GetCon());
- cmd.CommandType = ct;
- using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
- {
- dt.Load(sdr);
- }
- return dt;
- }
- }
下面是两中调用方法:
- public int logincheck(string admin, string pwd)
- {
- SqlParameter[] paras = new SqlParameter[] {
- new SqlParameter("@AdminName",admin),
- new SqlParameter("@Password",pwd)
- };
- int i = Convert.ToInt32(sqlhelper.ExecuteQuery("Admin_check_login", paras, CommandType.StoredProcedure).Rows[0][0].ToString());
- return i;
- }
这种是直接与数据库交互没有用到sqlhelper类
- DataRow dr;
- string sql = "SELECT PKID, User_Name, Password, User_Grade,(SELECT UserGrade FROM UserGrade WHERE pkid = user_grade) AS UG FROM Users WHERE (User_Name =@UserName) AND (Password = @Password)";
- SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString);
- SqlDataAdapter sqlAdapter1 = new SqlDataAdapter(sql, sqlConnection);
- sqlAdapter1.SelectCommand.Parameters.Add(new SqlParameter("@UserName", SqlDbType.NVarChar));
- sqlAdapter1.SelectCommand.Parameters.Add(new SqlParameter("@Password", SqlDbType.NVarChar));
- sqlAdapter1.SelectCommand.Parameters["@UserName"].Value = tbName.Text.Trim();
- sqlAdapter1.SelectCommand.Parameters["@Password"].Value = tbPwd.Text.Trim();
- DataSet product = new DataSet();
- sqlAdapter1.Fill(product,"Users");
- dr = product.Tables[0].Rows[0];
评论暂时关闭