C Sharp数据库简单操作.doc

c#快速开发数据库简单操作一、已有数据库（SQL）表，快速生成对应存储过程的工具。SQLPilot.exe二、已有数据库表，快速生成列表、基本查询、高级查询、插入新行、删除、编辑ASP.net页面的工具ASP.NET Maker。可参看网址：/aspnetmaker/数据库操作类，以下是C#代码：源代码文件如下：using System;using System.Web.UI;using System.Globalization;using System.Data;using System.Data.SqlClient;namespace ns_dbpublic class c_db: UserControl/*/ 返回数据库连接字符串/*public string ewConnStr()return Password=*;Persist Security Info=True;User ID=sa;Initial Catalog=*;Data Source=(Local); / End ewConnStr/*/ 返回一个 DataView 根据 Connection String & SQL/*public DataView ewDataView(string sConn, string sSQL)try/ Create a new Connection ObjectSqlConnection oConn = new SqlConnection(sConn);/ Create a new DataAdapter using the Connection Object and SQL statementSqlDataAdapter oDa = new SqlDataAdapter(sSQL, oConn);/ Create a new DataSet Object to fill with DataDataSet oDs = new DataSet();/ Fill the DataSet with Data from the DataAdapter ObjectoDa.Fill(oDs, ewDataSet);return oDs.Tables0.DefaultView;catch (SqlException oErr)Sessiondberrmsg = ewDataErrorMessage(oErr);return null; / End ewDataView/*/ 返回一个 DataView for Linking/*public DataView ewDataViewLink(string sConn, string sTable/*表名*/,string sLnkFld, string sDispFld,string sDispFld2, string sFilterFld,string sOrderBy/*排序列*/, string sOrderType/*排序规则*/,bool bDistinct/*是否剔除重复值*/, string sFilter)string sSQL;try/ Construct SQL statementsSQL = SELECT;if (bDistinct)sSQL += DISTINCT;sSQL += + sLnkFld + , + sDispFld + ;if (sDispFld2 != )sSQL += , + sDispFld2 + ;if (sFilterFld != )sSQL += , + sFilterFld + ;sSQL += FROM + sTable + ;if (sFilter != )sSQL += WHERE + sFilter;if (sOrderBy != )sSQL += ORDER BY + sOrderBy + + sOrderType;/ Create a new Connection Object using the Connection StringSqlConnection oConn = new SqlConnection(sConn);/ Create a new DataAdapter using the Connection Object and SQL statementSqlDataAdapter oDa = new SqlDataAdapter(sSQL, oConn);/ Create a new DataSet Object to fill with DataDataSet oDs = new DataSet();/ Fill the DataSet with Data from the DataAdapter ObjectoDa.Fill(oDs, ewDataSet);/ Create the TextField and ValueField ColumnsoDs.Tables0.Columns.Add(ewValueField,Type.GetType(System.String), + sLnkFld + );if (sDispFld2 = )oDs.Tables0.Columns.Add(ewTextField,Type.GetType(System.String), + sDispFld + );elseoDs.Tables0.Columns.Add(ewTextField,Type.GetType(System.String), + sDispFld + + , + + sDispFld2 + );return oDs.Tables0.DefaultView;catch (SqlException oErr)Sessiondberrmsg = ewDataErrorMessage(oErr);return null; / End ewDataViewLink/*/ 根据 Connection String & SQL 返回 Records 的数量/*public int ewRecordCount(string sConn, string sTable, string sWhere)string sSQL;try/ Construct SQLsSQL = SELECT COUNT(*) FROM + sTable + ;if (sWhere != )sSQL += WHERE + sWhere;/ Create a new Connection Object using the Connection StringSqlConnection oConn = new SqlConnection(sConn);/ Create a new DataAdapter using the Connection Object and SQL statementSqlDataAdapter oDa = new SqlDataAdapter(sSQL, oConn);/ Create a new DataSet object to fill with DataDataSet oDs = new DataSet();/ Fill the DataSet with Data from the DataAdapter ObjectoDa.Fill(oDs, ewDataSet);return Convert.ToInt32(oDs.Tables0.Rows00);catch (SqlException oErr)Sessiondberrmsg = ewDataErrorMessage(oErr);return 0; / End ewRecordCount/*/ 返回 1-page DataView 根据 Connection String & SQL/*public DataView ewDataViewPage(string sConn, string sSQL,int iCurrentRec, int iPageSize)try/ Create a new Connection Object using the Connection StringSqlConnection oConn = new SqlConnection(sConn);/ Create a new DataAdapter using the Connection Object and SQL statementSqlDataAdapter oDa = new SqlDataAdapter(sSQL, oConn);/ Create a new DataSet object to fill with DataDataSet oDs = new DataSet();/ Fill the DataSet with Data from the DataAdapter ObjectoDa.Fill(oDs, iCurrentRec, iPageSize, ewDataSet);return oDs.Tables0.DefaultView;catch (SqlException oErr)Sessiondberrmsg = ewDataErrorMessage(oErr);return null; / End ewDataViewPage/*/ Return a DataReader based on Connection & SQL/*public SqlDataReader ewDataReader(SqlConnection oConn, string sSQL)try/ Create a DataReader ObjectSqlDataReader oDr;/ Create a new Command Object using the Connection and SQL statementSqlCommand oCmd = new SqlCommand(sSQL, oConn);/ Execute the SQL statement against the Command to get the DataReaderoDr = oCmd.ExecuteReader();return oDr;catch (SqlException oErr)Sessiondberrmsg = ewDataErrorMessage(oErr);return null; / End ewDataReader/*/ Return Error Message based on Error Object/*public string ewDataErrorMessage(SqlException oErr)string sDbErrMsg;sDbErrMsg = ;for (int i = 0; i = oErr.Errors.Count - 1; i+)sDbErrMsg += Message: + oErr.Errorsi.Message + + Line Number: + oErr.Errorsi.LineNumber + + Source: + oErr.Errorsi.Source + + Procedure: + oErr.Errorsi.Procedure + ;return sDbErrMsg; / End ewDataErrorMessage/*/ Return Upload File Name/*public string ewUploadFileName(string sFileName)string sOutFileName;/ Amend your logic heresOutFileName = sFileName;/ Return computed output file namereturn sOutFileName; / End ewUploadFileName/*/ Return Formatted Number similar to VB FormatNumber/ - IncludeLeadingDigit is not supported/*public string ewFormatNumber(object oNo, int iDecPlace, int iUseParen, int iGroupDigits)NumberFormatInfo oNfi = (NumberFormatInfo) NumberFormatInfo.CurrentInfo.Clone();oNfi.NumberDecimalDigits = iDecPlace; / NumDigitsAfterDecimal/ IncludeLeadingDigit: not usedif (iUseParen = -1) / UseParensForNegativeNumbers: TrueoNfi.NumberNegativePattern = 0;else if (iUseParen = 0) / UseParensForNegativeNumbers: FalseoNfi.NumberNegativePattern = 1;if (iGroupDigits = -1) / GroupDigits: TrueoNfi.NumberGroupSeparator = ,;else if (iGroupDigits = 0) / GroupDigits: FalseoNfi.NumberGroupSeparator = ;/ Cast for different data typesif (oNo is short) / short return (short) oNo).ToString(n,oNfi); else if (oNo is ushort) / ushort return (ushort) oNo).ToString(n,oNfi); else if (oNo is int) / int return (int) oNo).ToString(n,oNfi); else if (oNo is uint) / uint return (uint) oNo).ToString(n,oNfi); else if (oNo is long) / long return (long) oNo).ToString(n,oNfi); else if (oNo is ulong) / ulong return (ulong) oNo).ToString(n,oNfi); else if (oNo is float) / float return (float) oNo).ToString(n,oNfi); else if (oNo is double) / double return (double) oNo).ToString(n,oNfi); else if (oNo is decimal) / decimal return (decimal) oNo).ToString(n,oNfi); else return (decimal) oNo).ToString(n,oNfi); /*/ Return Formatted Currency similar to VB FormatCurrency/ - IncludeLeadingDigit is not supported/*public string ewFormatCurrency(object oNo, int iDecPlace, int iUseParen, int iGroupDigits)NumberFormatInfo oNfi = (NumberFormatInfo) NumberFormatInfo.CurrentInfo.Clone();oNfi.CurrencyDecimalDigits = iDecPlace; / NumDigitsAfterDecimal/ IncludeLeadingDigit: not usedif (iUseParen = -1) / UseParensForNegativeNumbers: TrueoNfi.CurrencyNegativePattern = 0;else if (iUseParen = 0) / UseParensForNegativeNumbers: FalseoNfi.CurrencyNegativePattern = 1;if (iGroupDigits = -1) / GroupDigits: TrueoNfi.CurrencyGroupSeparator = ,;else if (iGroupDigits = 0) / GroupDigits: FalseoNfi.CurrencyGroupSeparator = ;/ Cast for different data typesif (oNo is short) / short return (short) oNo).ToString(c,oNfi); else if (oNo is ushort) / ushort return (ushort) oNo).ToString(c,oNfi); else if (oNo is int) / int return (int) oNo).ToString(c,oNfi); else if (oNo is uint) / uint return (uint) oNo).ToString(c,oNfi); else if (oNo is long) / long return (long) oNo).ToString(c,oNfi); else if (oNo is ulong) / ulong return (ulong) oNo).ToString(c,oNfi); else if (oNo is float) / float return (float) oNo).ToString(c,oNfi); else if (oNo is double) / double return (double) oNo).ToString(c,oNfi); else if (oNo is decimal) / decimal return (decimal) oNo).ToString(c,oNfi); else return (decimal) oNo).ToString(c,oNfi); /*/ Return Formatted Percent similar to VB FormatPercent/ - IncludeLeadingDigit is not supported/*public string ewFormatPercent(object oNo, int iDecPlace, int iUseParen, int iGroupDigits)NumberFormatInfo oNfi = (NumberFormatInfo) NumberFormatInfo.CurrentInfo.Clone();oNfi.CurrencyDecimalDigits = iDecPlace; / NumDigitsAfterDecimal/ IncludeLeadingDigit: not usedif (iUseParen = -1) / UseParensForNegativeNumbers: TrueoNfi.CurrencyNegativePattern = 0;else if (iUseParen = 0) / UseParensForNegat