using System;
using System.Data;
using System.Data.SqlClient;
public class Helper
{
public static void Main()
{
// Concatenation string
string strcon = "server = .;database = blog;uid = sa;pwd = 1";
SqlHelper helper = new SqlHelper(strcon);
// table employee There are neme(varchar(20)),age(int),sex(bit)3 A field ;
string sql = "select * from employee";
SqlDataReader reader = helper.Reader(sql,null);
using (reader)
{
while (reader.Read())
{
Console.WriteLine(reader["name"].ToString());
}
}
/*string sql = "insert into employee values(@name,@age,@sex)";
SqlParameter[] ps = new SqlParameter[]
{
new SqlParameter("@name",SqlDbType.VarChar,20),
new SqlParameter("@age",SqlDbType.Int),
new SqlParameter("@sex",SqlDbType.Int)
};
ps[0].Value = " Ms. Cheung ";
ps[1].Value = 40;
ps[2].Value = 1;
helper.ExecuteNonQuery(sql,ps);*/
}
}
public class SqlHelper
{
private SqlConnection con = null;
private SqlCommand cmd = null;
public SqlHelper (string strcon)
{
con = new SqlConnection(strcon);
cmd = new SqlCommand();
cmd.Connection = con;
}
// Perform initialization Command object
private void PreparedCommand(string sql,params SqlParameter[]param)
{
cmd.CommandText = sql;
// empty Parameteras Objects in the
cmd.Parameters.Clear();
if (param!=null)
{
foreach (SqlParameter p in param)
{
cmd.Parameters.Add(p);
}
}
con.Open();
}
/* Execute the non-query statement */
// A parameter
public int ExecuteNonQuery(string sql,params SqlParameter[] param)
{
PreparedCommand(sql,param);
int i = cmd.ExecuteNonQuery();
Close();
return i;
}
// There is no parameter
public int ExecuteNonQuery(string sql)
{
PreparedCommand(sql,null);
int i = cmd.ExecuteNonQuery();
Close();
return i;
}
/* Execute query statement */
// A parameter
public SqlDataReader Reader(string sql,params SqlParameter[] param)
{
PreparedCommand(sql,param);
return cmd.ExecuteReader();
}
// There is no parameter
public SqlDataReader Reader(string sql)
{
PreparedCommand(sql,null);
return cmd.ExecuteReader();
}
public void Open()
{
con.Open();
}
public void Close()
{
cmd.Dispose();
con.Close();
}
}