using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient;using System.Data;using System.Windows.Forms;using System.Drawing;namespace DAL{ public static class DbOperation { private static string connStr = "Data Source=127.0.0.1;Initial Catalog=Toy;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, ref ComboBox cb) { SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn); DataSet myDatset = new DataSet(); myAdap.Fill(myDatset); cb.Items.Clear(); for (int i = 0; i < myDatset.Tables[0].Rows.Count; i++) cb.Items.Add(myDatset.Tables[0].Rows[i][0].ToString()); cb.SelectedIndex = 0; myAdap.Dispose(); } 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; } //用于查询并绑定到datagridview public static void DbQuery(string cmdStr, ref DataGridView dv) { SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn); DataSet myDatset = new DataSet(); myAdap.Fill(myDatset); dv.DataSource = myDatset.Tables[0].DefaultView; dv.AllowUserToAddRows = false; dv.AllowUserToDeleteRows = false; dv.SelectionMode = DataGridViewSelectionMode.FullRowSelect; dv.ScrollBars = ScrollBars.Both; dv.RowsDefaultCellStyle.BackColor = Color.FromArgb(224,224,224) ; dv.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(255, 255, 255); // dv.AlternatingRowsDefaultCellStyle = } //用于查询并绑定到datagridview 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 void DbEditImage(string editStr,string myImage) { //SqlConnection conn = new SqlConnection(connStr); //conn.Open(); //SqlParameter para = new SqlParameter(myImage, SqlDbType.Image, bytes.Length); //para.Value = bytes; //SqlCommand ins_cmd = new SqlCommand(editStr + myImage + ")", conn); //ins_cmd.Parameters.Add(para); //ins_cmd.ExecuteNonQuery(); //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; } } //过滤非法字符 public static bool FilterIsSpecial(string str) { string flag = str; if (str == "") { return false ; } 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(" ", ""); if (str == flag) { return false; } else { return true; } } } }}