SQLHelper收集(二)
来源:互联网 发布:mysql 查询重复记录 编辑:程序博客网 时间:2024/06/05 11:29
- 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;
- using System.Web.Configuration;
- using System.Web.SessionState;
- public class DBClass
- {
- public static SqlConnection myConnection;
- public static SqlCommand myCommand;
- public static DataSet ds;
- public static DataTable dt;
- public static DataRow dr;
- public static SqlDataAdapter myAdapter;
- public static SqlDataAdapter myAdapter1;
- public static SqlDataReader myReader;
- protected static HttpResponse Response;
- protected static HttpSessionState Session;
- public DBClass()
- {
-
-
-
- }
-
- #region 打开数据库操作
- public static void Open()
- {
- string strConnection;
- if (myConnection == null)
- {
-
- myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"].ToString());
- myConnection.Open();
- }
- else
- {
- if (myConnection.State == ConnectionState.Closed)
- {
- if (myConnection.ConnectionString == "" || myConnection.ConnectionString == null)
- {
- strConnection = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
- myConnection.ConnectionString = strConnection;
- }
- myConnection.Open();
- }
- }
- }
- #endregion 关闭数据库操作
-
- #region 关闭数据库
- public static void Close()
- {
- if (ds != null)
- {
- ds.Clear();
- ds.Dispose();
- }
- if (myConnection.State == ConnectionState.Open)
- {
- try
- {
- myAdapter.Dispose();
- }
- catch (Exception)
- {
- }
- myConnection.Close();
- myConnection.Dispose();
- }
- }
-
- public static void CloseConnection()
- {
- if (myConnection.State == ConnectionState.Open)
- {
- try
- {
- myAdapter.Dispose();
- }
- catch (Exception)
- {
- }
- myConnection.Close();
- myConnection.Dispose();
- }
- }
- #endregion
- # region 释放资源
- public static void Dispose()
- {
- if (ds != null)
- {
- ds.Dispose();
- }
- if (myAdapter != null)
- {
- myAdapter.Dispose();
- }
- if (myAdapter1 != null)
- {
- myAdapter.Dispose();
- }
- if (myCommand != null)
- {
- myCommand.Dispose();
- }
- if (myReader != null)
- {
- if (myReader.IsClosed)
- {
- myReader.Close();
- }
- }
- }
- #endregion
-
- public static SqlConnection getconn()
- {
- SqlConnection conn;
- string connections = System.Configuration.ConfigurationSettings.AppSettings["Connectionstring"];
- conn = new SqlConnection(connections);
- return conn;
- }
-
- public static SqlCommand getCommand()
- {
- SqlCommand myCmd = new SqlCommand();
- return myCmd;
- }
- public static SqlCommand getCommand(string strArg)
- {
- SqlCommand myCmd = new SqlCommand(strArg);
- return myCmd;
- }
- public static SqlCommand getCommand(string strArg, SqlConnection connArg)
- {
- Open();
- SqlCommand myCmd = new SqlCommand(strArg, connArg);
- return myCmd;
- }
-
- public static void ExeSql(string str_Sql)
- {
- if (myConnection.State == ConnectionState.Closed)
- {
- Open();
- }
- myCommand = new SqlCommand(str_Sql, myConnection);
- myCommand.ExecuteNonQuery();
- myCommand.Dispose();
- CloseConnection();
- }
-
- public static void Fill(string str_Sql)
- {
- myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"].ToString());
- if (myConnection.State == ConnectionState.Closed)
- {
- Open();
- }
- myAdapter = new SqlDataAdapter(str_Sql, myConnection);
- ds = new DataSet();
- myAdapter.Fill(ds);
- }
- public static void FillAdd(string tabname, string str_Sql)
- {
- myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"].ToString());
- if (myConnection.State == ConnectionState.Closed)
- {
- Open();
- }
- myAdapter = new SqlDataAdapter(str_Sql, myConnection);
- if (ds == null)
- ds = new DataSet();
- myAdapter.Fill(ds, tabname);
- }
-
- public static int GetRowCount(string str_Sql)
- {
- int intCount;
- Fill(str_Sql);
- if (ds.Tables.Count < 1)
- {
- Close();
- intCount = 0;
- }
- else
- {
- intCount = ds.Tables[0].Rows.Count;
- }
- return intCount;
- }
- public static void GetRowRecord(string str_Sql)
- {
- Fill(str_Sql);
- if (ds.Tables.Count > 0)
- {
- if (ds.Tables[0].Rows.Count > 0)
- {
- dr = ds.Tables[0].Rows[0];
- }
- }
- myConnection.Close();
- myConnection.Dispose();
- }
-
- public static void BindDataList(string sql, DataList mydatalist)
- {
- Fill(sql);
- mydatalist.DataSource = ds.Tables[0].DefaultView;
- mydatalist.DataBind();
- }
- public static void BindRepeater(string sql, Repeater myrepeater)
- {
- Fill(sql);
- myrepeater.DataSource = ds.Tables[0].DefaultView;
- myrepeater.DataBind();
- }
-
-
-
-
-
-
- public static int SelectedIndex(DataGrid DG)
- {
- if (DG.SelectedIndex == -1)
- {
- return -1;
- }
- else
- return DG.SelectedIndex;
- }
-
-
-
-
-
-
- public static string SelectedCode(DataGrid DG)
- {
- string thisCode = null;
- if (DG.Items.Count == 0)
- {
- WriteMessage("目前没有数据,请添加!", true, true);
- return null;
- }
- if (SelectedIndex(DG) == -1)
- {
- WriteMessage("请选择一项!", true, true);
- return null;
- }
- else
- {
- if (DG.DataKeyField.ToString().Trim().Length != 0)
- {
- thisCode = DG.DataKeys[SelectedIndex(DG)].ToString().Trim();
- return thisCode;
- }
- else
- {
- WriteMessage("DataGrid没有设置DataKeyField,因此无法确定选择项!", true, true);
- return null;
- }
- }
- }
- public static void BindDataGrid(string strsql, DataGrid dg)
- {
- try
- {
- Open();
- Fill(strsql);
- dg.DataSource = ds.Tables[0].DefaultView;
- if (dg.CurrentPageIndex > dg.PageCount - 1)
- {
- if (dg.PageCount > 0)
- {
- dg.CurrentPageIndex = dg.PageCount - 1;
- }
- else
- {
- dg.CurrentPageIndex = 0;
- }
- }
- dg.DataBind();
- }
- catch (Exception e)
- {
- WriteMessage(e.Message.ToString().Trim(), true, true);
- }
- finally
- {
- Close();
- Dispose();
- }
- }
- public static void BindDataGrid1(string sql, DataGrid mydg)
- {
- Fill(sql);
- mydg.DataSource = ds.Tables[0].DefaultView;
- mydg.DataBind();
- }
-
-
-
-
- public static void DeleteDgNotice(DataGrid dg)
- {
- if ((dg.Items.Count % dg.PageSize == 1) && (dg.PageCount > 1))
- {
- if (dg.PageCount > 1)
- {
- dg.CurrentPageIndex = dg.CurrentPageIndex - 1;
- }
- else
- {
- dg.CurrentPageIndex = 0;
- }
- }
- }
-
-
-
-
-
-
- public static void BindDataGrid(string strsql, DataGrid dg, string ColumnName)
- {
- try
- {
- if (myConnection.State == ConnectionState.Closed)
- {
- Open();
- }
- Fill(strsql);
- ds.Tables[0].Columns.Add("KeyField");
- for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
- {
- ds.Tables[0].Rows[i]["KeyField"] = HttpUtility.UrlDecode(ds.Tables[0].Rows[i][ColumnName].ToString().Trim());
- dg.DataSource = ds.Tables[0].DefaultView;
- if (dg.CurrentPageIndex > dg.PageCount - 1)
- {
- if (dg.PageCount > 0)
- {
- dg.CurrentPageIndex = dg.PageCount - 1;
- }
- else
- {
- dg.CurrentPageIndex = 0;
- }
- }
- }
- dg.DataBind();
- }
- catch (Exception e)
- {
- WriteMessage(e.Message.ToString().Trim(), true, true);
- }
- finally
- {
- Close();
- Dispose();
- }
- }
- public static void BindDropDownList(string str_Text, string sql, DropDownList myDropDownList)
- {
-
-
- if (ds != null)
- {
- FillAdd("binddropdownlist" + sql.ToString(), sql);
- myDropDownList.DataSource = ds.Tables["binddropdownlist" + sql.ToString()].DefaultView;
- }
- else
- {
- Fill(sql);
- myDropDownList.DataSource = ds.Tables[0].DefaultView;
- }
- myDropDownList.DataValueField = str_Text;
- myDropDownList.DataTextField = str_Text;
- myDropDownList.DataBind();
- }
-
-
-
-
-
-
-
- public static void BindDropDownList(string str_Value, string str_Text, string sql, DropDownList myDropDownList)
- {
- try
- {
- if (myConnection.State == ConnectionState.Closed)
- {
- Open();
- }
- if (ds != null)
- {
- FillAdd("binddropdownlist" + sql.ToString(), sql);
- myDropDownList.DataSource = ds.Tables["binddropdownlist" + sql.ToString()].DefaultView;
- }
- else
- {
- Fill(sql);
- myDropDownList.DataSource = ds.Tables[0].DefaultView;
- }
- myDropDownList.DataValueField = str_Value;
- myDropDownList.DataTextField = str_Text;
- myDropDownList.DataBind();
- if (myDropDownList.Items.Count == 0)
- {
- ListItem li_null = new ListItem("无", "无");
- myDropDownList.Items.Add(li_null);
- }
- }
- catch (Exception e)
- {
- WriteMessage(e.Message.ToString().Trim(), true, true);
- }
- finally
- {
- Close();
- Dispose();
- }
- }
- public static void BindDropDownList(string str_Value, string str_Text, string sql, DropDownList myDropDownList, bool all)
- {
- if (ds != null)
- {
- FillAdd("binddropdownlist" + sql.ToString(), sql);
- if (all)
- {
- DataRow drL = ds.Tables["binddropdownlist" + sql.ToString()].NewRow();
- drL[str_Text] = "";
- drL[str_Value] = "";
- ds.Tables["binddropdownlist" + sql.ToString()].Rows.InsertAt(drL, 0);
- }
- myDropDownList.DataSource = ds.Tables["binddropdownlist" + sql.ToString()].DefaultView;
- }
- else
- {
- Fill(sql);
- if (all)
- {
- DataRow drL = ds.Tables[0].NewRow();
- drL[str_Text] = "";
- drL[str_Value] = "";
- ds.Tables[0].Rows.InsertAt(drL, 0);
- }
- myDropDownList.DataSource = ds.Tables[0].DefaultView;
- }
- myDropDownList.DataValueField = str_Value;
- myDropDownList.DataTextField = str_Text;
- myDropDownList.DataBind();
-
- }
-
-
-
-
-
-
-
-
- public static void SelectBindDropDownListValue(string str_Value, string str_Text, string str_Value_Field, string str_Sql, DropDownList myDropDownList)
- {
- BindDropDownList(str_Value, str_Text, str_Sql, myDropDownList);
- myDropDownList.Items[0].Selected = false;
- for (int i = 0; i < myDropDownList.Items.Count; i++)
- {
- if (str_Value_Field == myDropDownList.Items[i].Value)
- {
- myDropDownList.Items[i].Selected = true;
- break;
- }
- }
- }
-
-
-
-
- public static void WriteMessage(string strMsg, bool Back, bool End)
- {
- Response = HttpContext.Current.Response;
-
- strMsg = strMsg.Replace("'", "");
-
- strMsg = strMsg.Replace("/r/n", "");
- Response.Write("<script language=javascript>alert('" + strMsg + "')</script>");
- if (Back)
- {
- Response.Write("<script language=javascript>history.back();</script)");
- }
- if (End)
- {
- Response.End();
- }
- }
- }