ADO.net基础学习总结（二）

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

将连接字符串放入配置文件中

1、添加一个“应用程序配置文件：app.config”

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings >
<add name ="connStr" connectionString ="Data source =.;Initial Catalog =hkdb;User ID=sa;Password =3309578qian"/>
</connectionStrings>
</configuration>

使用方法：

2、添加对system.configuration的引用

3、定义一个变量接受app.config中的连接字符串

string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn=new SqlConnection (connStr ))
{
conn.Open();
}

SqlHelper类：封装ADO.net中重复的操作

ExcuteNonQuery方法：用于非查询sql语句（增删改）

private static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; public static int ExecuteNonQuery(string sql,params SqlParameter [] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}

            //ExecuteNonQuery方法的使用
            int a = SqlHelper.ExecuteNonQuery("insert into YG_Department(DepartmentID,DepartmentName,SuperiorDepartmentID) values (@DepartmentID,@DepartmentName,@SuperiorDepartmentID);",
                new SqlParameter("@DepartmentID", txbDepartmentID.Text), new SqlParameter("@DepartmentName", txbDepartmentName.Text), new SqlParameter("@SuperiorDepartmentID", txbSuperiorDepartmentID.Text));
            MessageBox.Show(a + "行受影响");

ExcuteScalar方法：用于查询一行一列的sql语句

public static object ExcuteScalar(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr ))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}

DataTable方法：用于查询多行数据的sql语句，一般查询中数据量都不是很大，所以大多数时候用DateTable而不是ExecuteReader方法，

public static DataTable DataTable(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataset);
return dataset.Tables[0];
}
}
}

            //DataTable方法的使用
            DataTable table = SqlHelper.DataTable("select * from YG_Department where DepartmentID =@DepartmentID;",
                new SqlParameter("@DepartmentID", txbDepartmentID.Text));
            foreach (DataRow row in table.Rows)
            {
                string name = (string)row["DepartmentName"];
                MessageBox .Show (name);
            }