BS架构数据库操作必备
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Text; using System.Data.SqlClient; using System.Data; using System.Web.UI.WebControls; using System.Windows.Forms; using System.Collections; //using System.Windows.Forms; namespace Test.Utility { public static class DbOperation { private static string connStr = "Data Source=58.57.32.11,1347;Initial Catalog=CRM;Persist Security Info=True;User ID=sa;Password=521777yesu!#%@$^"; public static string DbQueryCount(string cmdStr) { string result = ""; SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn); DataSet myDatset = new DataSet(); myAdap.Fill(myDatset); myAdap.Dispose(); int a = 0; int b = 0; a = myDatset.Tables[0].Rows.Count; b = myDatset.Tables[0].Columns.Count; for (int i = 0; i < a; i++) { for (int j = 0; j < b; j++) { result = result + myDatset.Tables[0].Rows[i][j].ToString(); result = result + " "; } result = result + "\n"; } return result; } //public static void checkListBind(string cmdStr, ref CheckedListBox clb) //{ // SqlConnection conn = new SqlConnection(connStr); // conn.Open(); // SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn); // DataSet myDatset = new DataSet(); // myAdap.Fill(myDatset); // for (int i = 0; i < myDatset.Tables[0].Rows.Count; i++) // clb.Items.Add(myDatset.Tables[0].Rows[i][0].ToString()); // //clb.SelectedIndex = 0; // myAdap.Dispose(); //} public static void ComboxBind(string cmdStr,string ViewName,string UserName, ref DropDownList dl) { int myCount = 0; SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn); myAdap.SelectCommand.CommandType = CommandType.StoredProcedure; SqlParameter spt1 = new SqlParameter("@MyCount", SqlDbType.VarChar, 20); spt1.Direction = ParameterDirection.Output; spt1.Value = myCount; myAdap.SelectCommand.Parameters.Add(spt1); SqlParameter spt2 = new SqlParameter("@ViewName", SqlDbType.VarChar, 20); spt2.Direction = ParameterDirection.Input; spt2.Value = ViewName; myAdap.SelectCommand.Parameters.Add(spt2); SqlParameter spt3 = new SqlParameter("@UserName", SqlDbType.VarChar, 20); spt3.Direction = ParameterDirection.Input; if (UserName == "") { dl.Visible = false; return; } spt3.Value = UserName; myAdap.SelectCommand.Parameters.Add(spt3); DataSet myDatset = new DataSet(); myAdap.Fill(myDatset); dl.Items.Clear(); for (int i = 0; i < myDatset.Tables[0].Rows.Count; i++) dl.Items.Add(myDatset.Tables[0].Rows[i][0].ToString()); dl.SelectedIndex = 0; myAdap.Dispose(); } //用于查询并绑定到datagridview public static void DbQuery(string cmdStr, string myParameter, ref GridView dv) { SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn); myAdap.SelectCommand.CommandType = CommandType.StoredProcedure; if (myParameter != "") { SqlDataAdapter myAdap1 = new SqlDataAdapter("GetPara", conn); int myCount = 0; myAdap1.SelectCommand.CommandType = CommandType.StoredProcedure; SqlParameter spt1 = new SqlParameter("@Para_Count", SqlDbType.VarChar, 20); spt1.Direction = ParameterDirection.Output; spt1.Value = myCount; myAdap1.SelectCommand.Parameters.Add(spt1); SqlParameter spt2 = new SqlParameter("@Sp_Name", SqlDbType.VarChar, 20); spt2.Direction = ParameterDirection.Input; spt2.Value = cmdStr; myAdap1.SelectCommand.Parameters.Add(spt2); DataSet myDataSet1 = new DataSet(); myAdap1.Fill(myDataSet1); string[] myArray = myParameter.Split(‘@‘); SqlParameter[] myList = new SqlParameter[myDataSet1.Tables[0].Rows.Count]; for (int i = 0; i < myDataSet1.Tables[0].Rows.Count; i++) { //myList[i].ParameterName = myDataSet1.Tables[0].Rows[i][0].ToString(); //MessageBox.Show("" + myDataSet1.Tables[0].Rows[i][0].ToString()); myList[i] = new SqlParameter(); myList[i].ParameterName = myDataSet1.Tables[0].Rows[i][0].ToString(); myList[i].SqlDbType = SqlDbType.NVarChar; myList[i].Size = 256; myList[i].Direction = ParameterDirection.Input; myList[i].Value = myArray[i]; myAdap.SelectCommand.Parameters.Add(myList[i]); } } DataSet myDatset = new DataSet(); myAdap.Fill(myDatset); if (myDatset.Tables.Count == 0) { return; } dv.DataSource = myDatset.Tables[0].DefaultView; dv.DataBind(); //dv.Columns[1].HeaderStyle.Width = new Unit("124"); myAdap.Dispose(); } //用于查询并绑定到datagridview public static string DbReturn(string cmdStr) { string returnStr = ""; SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlCommand myCmd = new SqlCommand(cmdStr, conn); if (myCmd.ExecuteScalar() != null) returnStr = myCmd.ExecuteScalar().ToString(); else returnStr = ""; return returnStr; } public static DataTable DbQueryTable(string cmdStr) { SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn); DataSet myDatset = new DataSet(); myAdap.Fill(myDatset); myAdap.Dispose(); return myDatset.Tables[0]; } public static long DbCount(string cmdStr) { int myCount = 0; SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlCommand mycmd = new SqlCommand(cmdStr, conn); myCount = (int)mycmd.ExecuteScalar(); conn.Close(); return myCount; } public static void DbEdit(string editStr) { SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlCommand mycmd = new SqlCommand(editStr, conn); mycmd.ExecuteScalar(); conn.Close(); } //过滤非法字符 public static string FilterSpecial(string str) { if (str == "") { return str; } else { str = str.Replace("‘", ""); str = str.Replace("<", ""); str = str.Replace(">", ""); str = str.Replace("%", ""); str = str.Replace("‘delete", ""); str = str.Replace("‘drop", ""); str = str.Replace("‘alter", ""); str = str.Replace("‘add", ""); str = str.Replace("‘‘", ""); str = str.Replace("\"\"", ""); str = str.Replace(",", ""); str = str.Replace(".", ""); str = str.Replace(">=", ""); str = str.Replace("=<", ""); str = str.Replace("-", ""); str = str.Replace("_", ""); str = str.Replace(";", ""); str = str.Replace("||", ""); str = str.Replace("[", ""); str = str.Replace("]", ""); str = str.Replace("&", ""); str = str.Replace("#", ""); str = str.Replace("/", ""); str = str.Replace("-", ""); str = str.Replace("|", ""); str = str.Replace("?", ""); str = str.Replace(">?", ""); str = str.Replace("?<", ""); //str = str.Replace(" ", ""); return str; } } } }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。