连接与关闭数据库
为了访问数据库,就要提供数据库连接类,在C#中是通过Connection类来实现的。它有4种类型的连接方式:
SQLConnection,ADOConnection,OracleConnection,ODBCConnection
这里只介绍连接SqlServer数据库的步骤:
(1)引用命名空间System.Data.SqlClient
(2)将连接方法声明至字符串中
windows登录:
string constr = "Server=.;integrated security=SSPI;Initial Catalog=DB_TEST";
sqlserver登录:
string constr1 = "Server=.;user=sa;pwd=find/-perm4000;database=DB_TEST";
string constr2="Data Source=.;Initial Catalog=RYTreasureDB;User ID=sa;pwd=sa;Integrated Security=True"
(3)创建Connection对象
SqlConnection sqlConnection = new SqlConnection(constr);
sqlConnection.Open();
(4)调用
I.直接执行一条无参sql(update,delete,insert,或者exec)
SqlCommand sqlCommand=new SqlCommand("update RecordInsure set ServerID=2 where RecordID=3534",sqlConnection);
//执行该sql,返回受影响的行数(可以执行任意curd,若执行的select,不论是否有查询结果,均返回-1)
int count=sqlCommand.ExecuteNonQuery();
II.执行有参sql(update,delete,insert)
SqlCommand sqlCommand=new SqlCommand("update RecordInsure set CollectNote=@param1 where RecordID=@param2",sqlConnection);
SqlParameter[] sqlParameters={new SqlParameter("@param1","测试有参sql"),new SqlParameter("@param2",111)};
sqlCommand.Parameters.AddRange(sqlParameters);
//受影响的行数
int count=sqlCommand.ExecuteNonQuery();
III.执行有参select语句
SqlCommand sqlCommand=new SqlCommand("select * from RecordInsure where RecordID=@param1",sqlConnection);
//给参数@param1赋值10
sqlCommand.Parameters.AddWithValue("@param1",10);
DataSet ds=new DataSet();
SqlDataAdapter sda=new SqlDataAdapter(sqlCommand);
sda.Fill(ds);
IV.调用无参、无输出参数、无返回值、无查询结果集的存储过程
SqlCommand sqlCommand=new SqlCommand("spTest1",sqlConnection);
sqlCommand.CommandType=CommandType.StoredProcedure;
//执行该sp,这里不再能反应影响的行数,始终返回-1
int count=sqlCommand.ExecuteNonQuery();
V.调用有参、无输出参数、无返回值、无查询结果集的存储过程
SqlCommand sqlCommand=new SqlCommand("spTest2",sqlConnection);
sqlCommand.CommandType=CommandType.StoredProcedure;
SqlParameter[] sqlParameters={new SqlParameter("@param1",100),new SqlParameter("@param2","调用了有参的存储过程")}
sqlCommand.Parameters.AddRange(sqlParameters);
sqlCommand.ExecuteNonQuery();
VI.调用有参、有输出参数、无返回值、无查询结果集的存储过程
SqlCommand sqlCommand=new SqlCommand("spTest3",sqlConnection);
sqlCommand.CommandType=CommandType.StoreProcedure;
SqlParameter[] sqlParameters={new SqlParameter("@param1",101),new SqlParameter("@param2","调用了有参有输出参数的存储过程")}
//输出参数单独设置,参数类型为varchar,长度为100
SqlParameter paramOutput=new SqlParameter("@param3",SqlDbType.VarChar,100);
paramOutput.Direction=ParameterDirection.Output;
sqlCommand.Parameters.AddRange(sqlParameters);
sqlCommand.Parameters.Add(paramOutput);
sqlCommand.ExecuteNonQuery();
//打印存储过程返回的输出参数
Console.WriteLine(paramOutput.value);
VII.调用有参、有输出参数、有返回值、无查询结果集的存储过程
SqlCommand sqlCommand=new SqlCommand("spTest4",sqlConnection);
sqlCommand.CommandType=CommandType.StoredProcedure;
SqlParameter[] sqlParameters={new SqlParameter("@param1",102),new SqlParameter("@param2","调用了有参有输出参数有返回值的存储过程")}
//输出参数单独设置,参数类型为varchar,长度为100
SqlParameter paramOutput=new SqlParameter("@param3",SqlDbType.VarChar,100);
paramOutput.Direction=ParameterDirection.Output;
//返回值参数单独设置,参数名可任意指定(存储过程也不要求参数名一致)
SqlParameter returnParam = new SqlParameter("@returnValue", SqlDbType.VarChar);
returnParam.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.AddRange(sqlParameters);
sqlCommand.Parameters.Add(paramOutPut);
sqlCommand.Parameters.Add(returnParam);
sqlCommand.ExecuteNonQuery();
//打印输出参数和返回值
Console.WriteLine(paramOutPut.Value);
Console.WriteLine(returnParam.Value);
VIII.调用有查询结果集的存储过程(前面的省略,不再说明)
SqlCommand sqlCommand=new SqlCommand("spTest5",sqlConnection);
sqlCommand.CommandType=CommandType.StoredProcedure;
SqlDataAdapter sda = new SqlDataAdapter(sqlCommand);
DataSet ds = new DataSet();
sda.Fill(ds);
sqlConnection.Close();
DataTable dt = ds.Tables[0];
//遍历DataTable
//获取字段名称
DataColumnCollection dcc = dt.Columns;
for(int i=0;i< dcc.Count; i++)
{
Console.Write(dcc[i].Caption+"\t");
}
Console.WriteLine();
//获取行记录
DataRowCollection drc = dt.Rows;
for(int i=0;i<drc.Count;i++)
{
for(int j=0;j<dcc.Count;j++)
{
Console.Write(drc[i][j]+"\t");
}
Console.WriteLine();
}
(5)调用结束
sqlConnection.Close();