数据库封装 sql server mysql_sqlserver数据库操作封装

1 public classSQLServerDatabase2 {3 private static SqlConnection m_Connection = null;4

5 public SQLServerDatabase(stringstrConnection)6 {7 m_Connection = newSqlConnection(strConnection);8 OpenConnection();9 }10 //链接数据库操作

11 public SQLServerDatabase(string strServerName, string strDatabaseName, string strUserName, string strPassword, boolbIntegratedSecurity)12 {13 string strConnection = "data source =" + strServerName + ";initial catalog =" +strDatabaseName;14 if(bIntegratedSecurity)15 {16 strConnection += ";Integrated Security = SSPI";17 }18 else

19 {20 strConnection += ";user id =";21 strConnection +=strUserName;22 strConnection += ";password =";23 strConnection +=strPassword;24 }25 m_Connection = newSqlConnection(strConnection);26 OpenConnection();27 }28 //根据sql语句获得datatable

29 public DataTable GetTableBySQL(string strSQL, bool bAddWithKey = false)30 {31 SqlCommand cmd = new SqlCommand(null, m_Connection);32 cmd.CommandType =CommandType.Text;33 cmd.CommandText =strSQL;34

35 SqlDataAdapter da = newSqlDataAdapter(cmd);36

37 DataTable dTable = newDataTable();38 da.Fill(dTable);39

40 returndTable;41 }42 //获得datarow根据sql语句

43 public DataRow GetRowBySQL(stringstrSQL)44 {45 DataTable dTable =GetTableBySQL(strSQL);46

47 if (dTable.Rows.Count == 0)48 return null;49 else

50 return dTable.Rows[0];51 }52 //执行sql语句

53 public void ExecuteSQL(stringstrSQL)54 {55 SqlCommand cmd = new SqlCommand(null, m_Connection);56 cmd.CommandType =CommandType.Text;57 cmd.CommandText =strSQL;58 cmd.ExecuteNonQuery();59 }60 //执行事务，传入sql列表

61 public void ExecuteNonQueryTransSql(ListlstSql)62 {63 SqlConnection conn =m_Connection;64 SqlTransaction sqlTran = null;65 SqlCommand cmd = newSqlCommand(); ;66 try

67 {68 sqlTran =conn.BeginTransaction(IsolationLevel.ReadCommitted);69 cmd.Connection =conn;70 cmd.CommandType =CommandType.Text;71 cmd.CommandTimeout = 1800;72 cmd.Transaction =sqlTran;73

74 foreach (String sql inlstSql)75 {76 cmd.CommandText =sql;77 cmd.ExecuteNonQuery();78 }79 sqlTran.Commit();80 }81 catch(Exception ex)82 {83 try

84 {85 if (sqlTran != null)86 {87 sqlTran.Rollback();88 }89 }90 catch

91 {92 }93 throwex;94 }95 finally

96 {97 cmd.Dispose();98 }99 }100 //获得最后的ID

101 public intGetLastID()102 {103 SqlCommand cmd = new SqlCommand(null, m_Connection);104 cmd.CommandType =CommandType.Text;105 cmd.CommandText = "select @@identity";106 returnSystem.Convert.ToInt32(cmd.ExecuteScalar());107 }108

109 //判断数据库库中是否有相应的表

110 public bool TableExists(stringstrTableName)111 {112 string sql = "select * from sysobjects where type='U' and name='" + strTableName + "'";113 SqlDataAdapter sqlda = newSqlDataAdapter(sql, m_Connection);114 DataSet ds = newDataSet();115 sqlda.Fill(ds);116 return (ds.Tables[0].Rows.Count != 0);117 }118 //得到表名称

119 public ListGetTableName()120 {121 List listTableName = new List();122

123 DataTable dt = GetTableBySQL("select name from sysobjects where type='U'");124 foreach (DataRow row indt.Rows)125 {126 listTableName.Add(row["name"].ToString());127 }128

129 returnlistTableName;130 }131

132

133 //判断字段是否存在

134 public bool FieldExists(string strTableName, stringstrFieldName)135 {136 string sql = "select * from syscolumns where id=object_id('" + strTableName + "') and name='" + strFieldName + "'";137 SqlDataAdapter sqlda = newSqlDataAdapter(sql, m_Connection);138 DataSet ds = newDataSet();139 sqlda.Fill(ds);140 return (ds.Tables[0].Rows.Count != 0);141 }142 //关闭链接

143 public voidCloseConnection()144 {145 m_Connection.Close();146 }147 //打开链接

148 public voidOpenConnection()149 {150 m_Connection.Open();151 }152

153 public System.Data.Common.DbCommand NewCommand(stringstrCommandText)154 {155 return newSqlCommand(strCommandText, m_Connection);156 }157 //获得表列

158 public DataTable GetColumnTable(stringstrTableName)159 {160 string sql = "select * from syscolumns where id=object_id('" + strTableName + "')";161 SqlDataAdapter sqlda = newSqlDataAdapter(sql, m_Connection);162 DataSet ds = newDataSet();163 sqlda.Fill(ds);164 return (ds.Tables[0]);165 }166 }