【C#】C#数据库操作部分记录(T-SQL)

1. 使用web.config配置文件存取连接字符串(SqlDataReader)

string constr = ConfigurationManager.ConnectionStrings["sqlconnstr"].ConnectionString;
SqlConnection conn = new SqlConnection(constr);
conn.Open();
SqlCommand cmd = new SqlCommand("select * from [User]", conn);
SqlDataReader dr = cmd.ExecuteReader();
while(dr.read())
{
    TextBox1.Text=dr["UserName"].ToString();
}
conn.Close();

2.使用SqlDataAdapter

string constr = ConfigurationManager.ConnectionStrings["sqlconnstr"].ConnectionString;
SqlConnection conn = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand("select * from [User]", conn);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
conn.Close();

3. SQL查询辅助方法（适用于多参数）

/// <summary>
/// 查询语句通用方法
/// </summary>
/// <param name="conn">数据库连接</param>
/// <param name="SQL_Text">查询字符串</param>
/// <param name="P">参数数组</param>
/// <param name="V">值数组</param>
/// <returns>查询到的表格</returns>
public DataTable DoSQL(SqlConnection conn,string SQL_Text,string [] P,string [] V)
{
    SqlCommand cmd = new SqlCommand(SQL_Text, conn);
    if (V!=null)
    {
        for (int i = 0; i < V.Length; i++)
        {
            cmd.Parameters.AddWithValue(P[i], V[i]);
        }
    }
    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
    //SqlDataAdapter 无需使用conn.open() 以及conn.close()
    DataTable table = new DataTable();
    sqlDataAdapter.Fill(table);
    return table;
}

//调用方法
1.无参数调用
DataTable table=DoSQL(conn,"select * from [User]",null,null);
2.含参数调用
DataTable table = DoSQL(conn, "select * from [User] where id=@id",new string[] { "@id"},new string[] {3.ToString()} );

4. 插入/更新语句辅助方法

public int DoInsertOrUpdateSQL(SqlConnection conn,string SQL_Text, string[] P, string[] V)
{
    SqlCommand cmd = new SqlCommand(SQL_Text, conn);
    if (V!=null)
    {
        for (int i = 0; i < V.Length; i++)
        {
            cmd.Parameters.AddWithValue(P[i], V[i]);
        }
    }
    int result = cmd.ExecuteNonQuery();
    return result;
}

调用语句参考查询通用方法的语句，int值为返回的修改记录条数。