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