0000.链接字符串配置信息保存于App.config中

1
2
3
4
<!--连接数据库字符串-->
<connectionStrings>
    <add name="DBStrConn" connectionString="Data Source=.; initial Catalog=数据库名; user id=用户名; password=密码" />
</connectionStrings>

0001.添加 System.configuration 引用

0010.在DBHelper中获取链接字符串

1
private static string strConnection = ConfigurationManager.ConnectionStrings["DBStrConn"].ConnectionString;//注意:此处的"DBStrConn"应与1中的name名字相对应。

0011.相关代码

0x00.执行SQL语句，返回影响的记录数-带参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = new SqlCommand(SQLString, connection))
        {
            try
            {
                if (cmdParms != null)
                {
                    cmd.Parameters.AddRange(cmdParms);
                }
                connection.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }
        }
    }
}

0x01.执行SQL语句，返回影响的记录数-无参数

1
2
3
4
public int ExecuteSql(string SQLString)
{
    return ExecuteSql(SQLString, null);
}

0x02.执行一条计算查询结果语句，返回查询结果（object）-带参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public object ExecuteScalar(string SQLString, params SqlParameter[] cmdParms)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = new SqlCommand(SQLString, connection))
        {
            try
            {
                if (cmdParms != null)
                {
                    cmd.Parameters.AddRange(cmdParms);
                }
                connection.Open();
                return cmd.ExecuteScalar();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }
        }
    }
}

0x03.执行一条计算查询结果语句，返回查询结果（object）-无参

1
2
3
4
public object ExecuteScalar(string SQLString)
{
    return ExecuteScalar(SQLString, null);
}

0x04.执行查询，返回SqlDataReader(注意：调用该方法后，一定要对SqlDataReader进行Close)-有参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
{
    SqlConnection connection = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand(SQLString, connection);
    try
    {
        if (cmdParms != null)
        {
            cmd.Parameters.AddRange(cmdParms);
        }
        connection.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
    catch (System.Data.SqlClient.SqlException e)
    {
        throw e;
    }
    finally
    {
        cmd.Dispose();
        connection.Close();
    }
}

0x05.执行查询，返回SqlDataReader(注意：调用该方法后，一定要对SqlDataReader进行Close)-无参数

1
2
3
4
public SqlDataReader ExecuteReader(string SQLString)
{
    return ExecuteReader(SQLString, null);
}

0x06.执行查询语句，返回DataSet-有参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
public DataSet GetDataSet(string SQLString, params SqlParameter[] cmdParms)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand(SQLString, connection);
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            if (cmdParms != null)
            {
                da.SelectCommand.Parameters.AddRange(cmdParms);
            }
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                cmd.Parameters.Clear();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
    }
}

0x07.执行查询语句，返回DataSet-无参数

1
2
3
4
public DataSet GetDataSet(string SQLString)
{
    return GetDataSet(SQLString, null);
}

0x08.执行查询语句，返回DataTable-有参数

1
2
3
4
public DataTable GetDataTable(string SQLString, params SqlParameter[] cmdParms)
{
    return GetDataSet(SQLString, cmdParms).Tables[0];
}

0x09.执行查询语句，返回DataTable-无参数

1
2
3
4
public DataTable GetDataTable(string SQLString)
{
    return GetDataTable(SQLString, null);
}