程序
一、已有数据库（SQL）表，快速生成对应存储过程的工具。SQLPilot.exe
SQLPilot.exe这个程序是CCF的一个朋友写的，具体请参考http://bbs.et8.net/bbs/showthread.php?t=534183
二、已有数据库表，快速生成列表、基本查询、高级查询、插入新行、删除、编辑asp.net页面的工具Asp.Net Maker
http://www.hkvstore.com/aspnetmaker/
1.数据库操作类
以下是Ｃ＃代码：
头文件:
using System;using System.Web.UI;using System.Globalization;using System.Data;using System.Data.SqlClient;namespace ns_db{public 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 Object SqlConnection oConn = new SqlConnection(sConn);
// Create a new DataAdapter using the Connection Object and SQL statement SqlDataAdapter oDa = new SqlDataAdapter(sSQL, oConn);
// Create a new DataSet Object to fill with Data DataSet oDs = new DataSet();
// Fill the DataSet with Data from the DataAdapter Object oDa.Fill(oDs, "ewDataSet"); return oDs.Tables[0].DefaultView; } catch (SqlException oErr) { Session["dberrmsg"] = 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 statement sSQL = "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 String SqlConnection oConn = new SqlConnection(sConn);
// Create a new DataAdapter using the Connection Object and SQL statement SqlDataAdapter oDa = new SqlDataAdapter(sSQL, oConn);
// Create a new DataSet Object to fill with Data DataSet oDs = new DataSet();
// Fill the DataSet with Data from the DataAdapter Object oDa.Fill(oDs, "ewDataSet");
// Create the TextField and ValueField Columns oDs.Tables[0].Columns.Add("ewValueField",Type.GetType("System.String"),"[" + sLnkFld + "]"); if (sDispFld2 == "") { oDs.Tables[0].Columns.Add("ewTextField",Type.GetType("System.String"),"[" + sDispFld + "]"); } else { oDs.Tables[0].Columns.Add("ewTextField",Type.GetType("System.String"),"[" + sDispFld + "] +
', ' + [" + sDispFld2 + "]"); } return oDs.Tables[0].DefaultView; } catch (SqlException oErr) { Session["dberrmsg"] = ewDataErrorMessage(oErr); return null; } } // End ewDataViewLink
//********************************************************* // 根据 Connection String & SQL 返回 Records 的数量 //*********************************************************
public int ewRecordCount(string sConn, string sTable, string sWhere) { string sSQL; try {
// Construct SQL sSQL = "SELECT COUNT(*) FROM [" + sTable + "]"; if (sWhere != "") { sSQL += " WHERE " + sWhere; }
// Create a new Connection Object using the Connection String SqlConnection oConn = new SqlConnection(sConn);
// Create a new DataAdapter using the Connection Object and SQL statement SqlDataAdapter oDa = new SqlDataAdapter(sSQL, oConn);
// Create a new DataSet object to fill with Data DataSet oDs = new DataSet();
// Fill the DataSet with Data from the DataAdapter Object oDa.Fill(oDs, "ewDataSet"); return Convert.ToInt32(oDs.Tables[0].Rows[0][0]); } catch (SqlException oErr) { Session["dberrmsg"] = 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 String SqlConnection oConn = new SqlConnection(sConn);
// Create a new DataAdapter using the Connection Object and SQL statement SqlDataAdapter oDa = new SqlDataAdapter(sSQL, oConn);
// Create a new DataSet object to fill with Data DataSet oDs = new DataSet();
// Fill the DataSet with Data from the DataAdapter Object oDa.Fill(oDs, iCurrentRec, iPageSize, "ewDataSet"); return oDs.Tables[0].DefaultView; } catch (SqlException oErr) { Session["dberrmsg"] = ewDataErrorMessage(oErr); return null; } } // End ewDataViewPage
//************************************************* // Return a DataReader based on Connection & SQL //*************************************************
public SqlDataReader ewDataReader(SqlConnection oConn, string sSQL) { try {
// Create a DataReader Object SqlDataReader oDr;
// Create a new Command Object using the Connection and SQL statement SqlCommand oCmd = new SqlCommand(sSQL, oConn);
// Execute the SQL statement against the Command to get the DataReader oDr = oCmd.ExecuteReader(); return oDr; } catch (SqlException oErr) { Session["dberrmsg"] = 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.Errors[i].Message + "" + "Line Number: " + oErr.Errors[i].LineNumber + "" + "Source: " + oErr.Errors[i].Source + "" + "Procedure: " + oErr.Errors[i].Procedure + ""; } return sDbErrMsg; } // End ewDataErrorMessage
//*************************** // Return Upload File Name //***************************
public string ewUploadFileName(string sFileName) { string sOutFileName;
// Amend your logic here sOutFileName = sFileName;
// Return computed output file name return 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 used if (iUseParen == -1) // UseParensForNegativeNumbers: True { oNfi.NumberNegativePattern = 0; } else if (iUseParen == 0) // UseParensForNegativeNumbers: False { oNfi.NumberNegativePattern = 1; } if (iGroupDigits == -1) // GroupDigits: True { oNfi.NumberGroupSeparator = ","; } else if (iGroupDigits == 0) // GroupDigits: False { oNfi.NumberGroupSeparator = ""; }
// Cast for different data types if (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 used if (iUseParen == -1) // UseParensForNegativeNumbers: True { oNfi.CurrencyNegativePattern = 0; } else if (iUseParen == 0) // UseParensForNegativeNumbers: False { oNfi.CurrencyNegativePattern = 1; } if (iGroupDigits == -1) // GroupDigits: True { oNfi.CurrencyGroupSeparator = ","; } else if (iGroupDigits == 0) // GroupDigits: False { oNfi.CurrencyGroupSeparator = ""; }
// Cast for different data types if (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 used if (iUseParen == -1) // UseParensForNegativeNumbers: True { oNfi.CurrencyNegativePattern = 0; } else if (iUseParen == 0) // UseParensForNegativeNumbers: False { oNfi.CurrencyNegativePattern = 1; } if (iGroupDigits == -1) // GroupDigits: True { oNfi.CurrencyGroupSeparator = ","; } else if (iGroupDigits == 0) // GroupDigits: False { oNfi.CurrencyGroupSeparator = ""; }
// Cast for different data types if (oNo is short) // short { return ((short) oNo).ToString("p",oNfi); } else if (oNo is ushort) // ushort { return ((ushort) oNo).ToString("p",oNfi); } else if (oNo is int) // int { return ((int) oNo).ToString("p",oNfi); } else if (oNo is uint) // uint { return ((uint) oNo).ToString("p",oNfi); } else if (oNo is long) // long { return ((long) oNo).ToString("p",oNfi); } else if (oNo is ulong) // ulong { return ((ulong) oNo).ToString("p",oNfi); } else if (oNo is float) // float { return ((float) oNo).ToString("p",oNfi); } else if (oNo is double) // double { return ((double) oNo).ToString("p",oNfi); } else if (oNo is decimal) // decimal { return ((decimal) oNo).ToString("p",oNfi); } else { return ((decimal) oNo).ToString("p",oNfi); } }} // End Class} // End NameSpace