以前群里有个人发过一个老外写的,有很多是关于XML操作的,大部分用不上,还是用自己总结出来的好,注释还不是很完善,不过应该很容易懂。
学习的结晶,本着开源的精神发出来大家改改,以求更加完善合理。
数据库连接字串放在web.config里,因为之前的项目很多是要进行对Gridview的结果绑定,所以应用了重载,如果要对Gridview1绑定select * from user的结果的话,就可以:
UserDB.CommonSelect("select * from user",GridView1);
如果不加GridView1的参数的话就会返回一个DataAdapter对象,方便后续的操作。
IsExistInDB是用来看表中是否存在某一个数据,用法如下:
如果要看user表里name列有没有一个用户叫carrie的
UserDB.IsExistInDB("user","name=carrie");
返回True说明有,反之亦然。
using
System;
using
System.Data;
using
System.Configuration;
using
System.Web;
using
System.Web.Security;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using
System.Web.UI.HtmlControls;
using
System.Data.SqlClient;
/**/
/// <summary>
/// UserDB 的摘要说明
/// </summary>
public
class
UserDB
...
{
public UserDB()
...{
//
// TODO: 在此处添加构造函数逻辑
//
}
/**//// <summary>
/// 根据条件查询是否存在相应的记录
/// </summary>
/// <param name="TableName">表名(不带空格)</param>
/// <param name="WhereIs">条件语句(不含where,可不带空格)</param>
///
public static bool IsExistInDB(string Select_Table_Name,string Select_Where_Is)
...{
string Conn_Db_Str = ConfigurationManager.AppSettings.Get("mydatabase");
string Select_Str = "SELECT * FROM ";
Select_Str += Select_Table_Name;
Select_Str += " WHERE ";
Select_Str += Select_Where_Is;
SqlConnection cnn = new SqlConnection(Conn_Db_Str );
SqlDataAdapter ada = new SqlDataAdapter(Select_Str , cnn);
DataSet ds = new DataSet();
ada.Fill(ds, "IsExistInDB");
if (ds.Tables["IsExistInDB"].Rows.Count == 0)
...{
return false;
}
return true;
}
/**//// <summary>
/// 整条SQL语句查询数据库,返回SqlDataAdapter。
/// </summary>
/// <param name="Select_Whole_Sentence">表名(不带空格)</param>
/// <returns>SqlDataAdapter类型</returns>
public static SqlDataAdapter CommonSelect(string Select_Whole_Sentence)
...{
string Conn_Db_Str = ConfigurationManager.AppSettings.Get("mydatabase");
string Select_Str = Select_Whole_Sentence;
SqlConnection cnn = new SqlConnection(Conn_Db_Str);
SqlDataAdapter ada = new SqlDataAdapter(Select_Str, cnn);
return ada;
}
public static void CommonSelect(string Select_Whole_Sentence,GridView gd)
...{
string Conn_Db_Str = ConfigurationManager.AppSettings.Get("mydatabase");
string Select_Str = Select_Whole_Sentence;
SqlConnection cnn = new SqlConnection(Conn_Db_Str);
SqlDataAdapter ada = new SqlDataAdapter(Select_Str, cnn);
DataSet ds=new DataSet ();
try
...{
ada.Fill(ds);
gd.DataSource = ds.Tables[0].DefaultView;
gd.DataBind();
}
catch (Exception ee)
...{
}
}
/**//// <summary>
/// SQL语句查询数据库,返回SqlDataAdapter。
/// </summary>
/// <param name="Select_Table_Name">表名(不带空格)</param>
/// <param name="Select_Where_Is">条件语句</param>
/// <returns>SqlDataAdapter类型</returns>
public static SqlDataAdapter CommonSelect(string Select_Table_Name, string Select_Where_Is)
...{
string Conn_Db_Str = ConfigurationManager.AppSettings.Get("mydatabase");
string Select_Str = "SELECT * FROM ";
Select_Str += Select_Table_Name;
Select_Str += " WHERE ";
Select_Str += Select_Where_Is;
SqlConnection cnn = new SqlConnection(Conn_Db_Str);
SqlDataAdapter ada = new SqlDataAdapter(Select_Str, cnn);
return ada;
}
/**//// <summary>
/// SQL语句查询数据库,绑定gridwiew。
/// </summary>
/// <param name="Select_Table_Name">表名(不带空格)</param>
/// <param name="Select_Where_Is">条件语句</param>
/// <param name="gd">gridview类型</param>
public static void CommonSelect(string Select_Table_Name, string Select_Where_Is,GridView gd)
...{
string Conn_Db_Str = ConfigurationManager.AppSettings.Get("mydatabase");
string Select_Str = "SELECT * FROM ";
Select_Str += Select_Table_Name;
Select_Str += " WHERE ";
Select_Str += Select_Where_Is;
SqlConnection cnn = new SqlConnection(Conn_Db_Str);
SqlDataAdapter ada = new SqlDataAdapter(Select_Str, cnn);
DataSet ds = new DataSet();
ada.Fill(ds);
gd.DataSource = ds.Tables[0].DefaultView;
gd.DataBind();
}
/**//// <summary>
/// SQL语句查询数据库,绑定repeater。
/// </summary>
/// <param name="Select_Table_Name">表名(不带空格)</param>
/// <param name="Select_Where_Is">条件语句</param>
public static void CommonSelect(string Select_Table_Name, string Select_Where_Is, Repeater rp)
...{
string Conn_Db_Str = ConfigurationManager.AppSettings.Get("mydatabase");
string Select_Str = "SELECT * FROM ";
Select_Str += Select_Table_Name;
Select_Str += " WHERE ";
Select_Str += Select_Where_Is;
SqlConnection cnn = new SqlConnection(Conn_Db_Str);
SqlDataAdapter ada = new SqlDataAdapter(Select_Str, cnn);
DataSet ds = new DataSet();
ada.Fill(ds);
rp.DataSource = ds.Tables[0].DefaultView;
rp.DataBind();
}
public static Boolean CommonInsert(string Insert_Whole_Sentence)
...{
string Conn_Db_Str = ConfigurationManager.AppSettings.Get("mydatabase");
string Insert_Str = Insert_Whole_Sentence;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(Insert_Str);
da.SelectCommand.Connection = new SqlConnection(Conn_Db_Str);
try
...{
da.SelectCommand.Connection.Open();
da.SelectCommand.ExecuteNonQuery();
return true;
}
catch (Exception ee)
...{
HttpContext.Current.Response.Write(ee.Message.ToString());
return false;
}
finally
...{
da.SelectCommand.Connection.Close();
}
//da.SelectCommand.CommandText = "NameOfProcedure";
//da.SelectCommand.CommandType = CommandType.StoredProcedure;
}
/**//// <summary>
/// SQL语句删除一条记录。
/// </summary>
/// <param name="Delete_Whole_Sentence">整条语句</param>
public static Boolean CommonDelete(string Delete_Whole_Sentence)
...{
string Conn_Db_Str = ConfigurationManager.AppSettings.Get("mydatabase");
string Delete_Str = Delete_Whole_Sentence;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(Delete_Str);
da.SelectCommand.Connection = new SqlConnection(Conn_Db_Str);
try
...{
da.SelectCommand.Connection.Open();
da.SelectCommand.ExecuteNonQuery();
return true;
}
catch (Exception ee)
...{
HttpContext.Current.Response.Write(ee.Message.ToString());
return false;
}
finally
...{
da.SelectCommand.Connection.Close();
}
}
/**//// <summary>
/// SQL语句删除一条记录。
/// </summary>
/// <param name="Delete_Table_name">表名(不带空格)</param>
/// <param name="Delete_Where_Is">条件语句</param>
public static Boolean CommonDelete(string Delete_Table_name,string Delete_Where_Is)
...{
string Conn_Db_Str = ConfigurationManager.AppSettings.Get("mydatabase");
string Delete_Str = "Delete "+Delete_Table_name +" Where "+Delete_Where_Is ;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(Delete_Str);
da.SelectCommand.Connection = new SqlConnection(Conn_Db_Str);
try
...{
da.SelectCommand.Connection.Open();
da.SelectCommand.ExecuteNonQuery();
return true;
}
catch (Exception ee)
...{
HttpContext.Current.Response.Write(ee.Message.ToString());
return false;
}
finally
...{
da.SelectCommand.Connection.Close();
}
}
public static int CommentExecute(string Select_Whole_Sentence)
...{
string Conn_Db_Str = ConfigurationManager.AppSettings.Get("mydatabase");
string Select_Str = Select_Whole_Sentence;
SqlConnection cnn = new SqlConnection(Conn_Db_Str);
SqlDataAdapter ada = new SqlDataAdapter(Select_Str, cnn);
DataSet ds = new DataSet();
ada.Fill(ds, "0");
return ds.Tables [0].Rows .Count ;
}
}
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1148055