一、已有数据库（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
|
本类阅读TOP10