1.连接数据库
using System.Data.SqlClient;
using System.Data;
using System.IO;
//连接字串
String connectionString="server=localhost;database=Northwind;uid=sa;pwd=sa";
//创建SqlConnection对象
SqlConnection mySqlConnection=new SqlConnection(connectionString);
2.添加数据
2.1 纯SQL语句方式
try
{
string strInsert = String.Format("insert into PLUS_NUMBER values('{0}',{1})",Numbers[n],smsId);
con.Open(); //打开连接
SqlCommand insertCommand = new SqlCommand(); //创建SQLCommand对象
insertCommand.Connection = con; //设置SqlCommand对象的连接
insertCommand.CommandText = strInsert; //设置SqlCommand对象SQL语句
int nResult = insertCommand.ExecuteNonQuery(); //执行
if(nResult <= 0)
{
//return **;
}
}
catch (System.Exception e)
{
e.ToString();
}
finally
{
try{
con.Close(); //关闭连接
}catch{
}
}
2.2 参数方式
try
{
con.Open();
string strInsert = "Insert into PLUS_SMS values (@beginTime,@endTime,@senderCode,@cpCode,@smsContent,0)";
SqlCommand insertCommand = con.CreateCommand();
insertCommand.CommandText = strInsert;
//SqlParameter Para1 = new SqlParameter("@Top", SqlDbType.VarChar, 25);
//SqlParameter Para2 = new SqlParameter("@OrderBy", SqlDbType.VarChar, 50);
//SqlParameter Para3 = new SqlParameter("@CategoryID", SqlDbType.VarChar, 25);
//insertCommand.SelectCommand.Parameters.Add(Para1);
//insertCommand.SelectCommand.Parameters.Add(Para2);
//insertCommand.SelectCommand.Parameters.Add(Para3);
//Para1.Value = "100 PERCENT";
//Para2.Value = OrderBy;
//Para3.Value = "-100";
insertCommand.Parameters.Add("@beginTime",SqlDbType.DateTime,5);
insertCommand.Parameters.Add("@endTime",SqlDbType.DateTime,5);
insertCommand.Parameters.Add("@senderCode",SqlDbType.VarChar,10);
insertCommand.Parameters.Add("@cpCode",SqlDbType.VarChar,10);
insertCommand.Parameters.Add("@smsContent",SqlDbType.VarChar,500);
insertCommand.Parameters["@beginTime"].Value = bcData.BeginTime;
insertCommand.Parameters["@endTime"].Value = bcData.EndTime;
insertCommand.Parameters["@senderCode"].Value = bcData.SenderCode;
insertCommand.Parameters["@cpCode"].Value = bcData.CPCode;
insertCommand.Parameters["@smsContent"].Value = bcData.SmsContent;
int nResult = insertCommand.ExecuteNonQuery();
if(nResult <= 0){
}
}
catch (System.Exception e)
{
e.ToString();
}finally
{
try{
con.Close(); //关闭连接
}catch{
}
}
3.读取数据
3.1 SqlCommand方式 适合单值
try
{
con.Open();
String sqlString = "select IDENT_CURRENT('PLUS_SMS')";
SqlCommand getIdCommand = new SqlCommand(sqlString,con);
int smsID = System.Int32.Parse( getIdCommand.ExecuteScalar().ToString() ); //读取返回的INT值
}
catch (System.Exception e)
{
e.ToString();
}
finally
{
try{
con.Close(); //关闭连接
}catch{
}
}
3.2 SqlDataReader方式 适合多值
try
{
con.Open();
String sqlString = "select IDENT_CURRENT('PLUS_SMS')";
SqlCommand sqlCommand = new SqlCommand(sqlString,con);
SqlDataReader sdr = sqlCommand.ExecuteReader();
while(sdr.Read())
{
sdr.GetValue(0); //读值 还可以使用GetString GetInt等方式
}
sdr.Close();//关闭reader
}
catch (System.Exception e)
{
e.ToString();
}
finally
{
try{
con.Close(); //关闭连接
}catch{
}
}
3.3 适配器方式
try
{
//使用适配器时,不需要打开连接
String strSelect = "Select * from PLUS_SMS order by s_id desc";
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(strSelect,con);
DataSet ds = new DataSet();
sqlDataAdapter.Fill(ds);
return ds;
}
catch (System.Exception e)
{
e.ToString();
}
finally
{
try{
con.Close(); //关闭连接
}catch{
}
}
4.调用存储过程
4.1 SqlCommand方式
SqlCommand1 = new SqlCommand("spCustomersLogin", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
//设置为储存方式
SqlParameter Param1 = new SqlParameter("@Email", SqlDbType.VarChar, 100);
SqlParameter Param2 = new SqlParameter("@Password", SqlDbType.VarChar, 50);
SqlParameter Param3 = new SqlParameter("@ID", SqlDbType.Int, 4);
SqlCommand1.Parameters.Add(Param1);
SqlCommand1.Parameters.Add(Param2);
SqlCommand1.Parameters.Add(Param3);
Param1.Value = sEmail;
Param2.Value = sPassword;
Param3.Direction = ParameterDirection.Output;
//设置为Output参数
4.2 适配器方式
SqlDataAdapter1 = new SqlDataAdapter("spProductsTop", SqlConnection1);
SqlDataAdapter1.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter Para1 = new SqlParameter("@Top", SqlDbType.VarChar, 25);
SqlParameter Para2 = new SqlParameter("@OrderBy", SqlDbType.VarChar, 50);
SqlParameter Para3 = new SqlParameter("@CategoryID", SqlDbType.VarChar, 25);
SqlDataAdapter1.SelectCommand.Parameters.Add(Para1);
SqlDataAdapter1.SelectCommand.Parameters.Add(Para2);
SqlDataAdapter1.SelectCommand.Parameters.Add(Para3);
Para1.Value = "100 PERCENT";
Para2.Value = OrderBy;
Para3.Value = "-100";
DataSet1 = new DataSet();
SqlDataAdapter1.Fill(DataSet1);