#region 執行查詢語句返回影響行數
public static int ExecuteNonQuery(string str_cmd,out string str_error)
{
int i=0;
str_error=string.Empty;
SqlConnection conn=CreateConn();
SqlCommand cmd=new SqlCommand(str_cmd,conn);
try
{
conn.Open();
i=cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
str_error=ex.Message;
}
finally
{
conn.Close();
}
return i;
}
#endregion
#region 返回第一行第一列
public static string ExecuteScalar(string str_cmd,out string str_error)
{
string str_value=string.Empty;
str_error=string.Empty;
SqlConnection conn=CreateConn();
SqlCommand cmd=new SqlCommand(str_cmd,conn);
try
{
conn.Open();
str_value=cmd.ExecuteScalar().ToString();
}
catch(Exception ex)
{
str_error=ex.Message;
}
finally
{
conn.Close();
}
return str_value;
}
#endregion
#region 返回datatbale from datareader
public static DataTable ExecuteReader(string str_cmd,out string str_error)
{
DataTable dt=new DataTable();
SqlDataReader dr=null;
str_error=string.Empty;
SqlConnection conn=CreateConn();
SqlCommand cmd=new SqlCommand(str_cmd,conn);
try
{
conn.Open();
dr=cmd.ExecuteReader();
dt=DataReaderToTable(dr);
}
catch(Exception ex)
{
str_error=ex.Message;
}
finally
{
conn.Close();
}
return dt;
}
#region 循環SqlDataReader填入Table
private static DataTable DataReaderToTable(SqlDataReader dr)
{
DataTable dt=new DataTable();
int count=dr.FieldCount;
int index=1;
if(count>0)
{
for(int i=0;i<count;i++)
{
dt.Columns.Add(dr.GetName(i),dr.GetFieldType(i));
}
dt.Columns.Add("id");//編號
dt.BeginLoadData();
while(dr.Read())
{
object[] objectRow=new object[count+1];
for(int i=0;i<count;i++)
{
objectRow[i]=dr[dr.GetName(i)];
}
objectRow[count]=index;//編號
dt.LoadDataRow(objectRow,true);
index++;
}
dt.EndLoadData();
}
return dt;
}
#endregion
public static SqlConnection CreateConn()
{
//string str_conn="server=.;database=bidding;user id=sa;pwd=jhc;";
SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnStr"]);
return conn;
}