ADONET的使用

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);