vs.net+c#程序快速开发数据库简单操作

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