Data | database using System;
Using System.Data;
Using System.Data.SqlClient;
Using System.Data.SqlTypes;
Using System.Windows.Forms;
Using System.Collections;
Namespace Database
{
<summary>
A summary description of the Database.
</summary>
public class Database
{
<summary>
Property
</summary>
Public DataSet DataSet
// {
Get
// {
return m_dataset;
// }
//
// }
Public Database ()
{
//
TODO: Add constructor logic here
//
Xmlread constr=new Xmlread ();
if (Constr.readallconnectnode ())
{
Constr= constr.connstring;
Try
// {
//
Open ();
// }
catch (Exception Ex)
// {
MessageBox.Show ("Database connection Error" +ex.tostring ());
//
// }
}
Else
{
Constr= "-1";
throw new Sqlerrorcollection ();
}
}
public bool Open ()
// {
//
MCN. ConnectionString = Constr;
Try
// {
MCN. Open ();
//
// }
catch (Exception)
// {
return false;
// }
return true;
// }
<summary>
Default Fetch DataSet
</summary>
<param name= "Pmytablename" ></param>
<param name= "Tmpmycomputername" ></param>
<returns></returns>
public virtual int GetData (string pmytablename, String tmpmycomputername)
// {
return-1;
//
// }
#region ExecuteNonQuery
<summary>
Executes an SQL Command (using connectstring)
</summary>
<param name= "connstring" >connectstring (SQL connection string) </param>
<param name= "Cmdtype" >command type </param>
<param name= "Cmdtext" >command statement (SQL statement) </param>
<param name= "cmdparms" >command parameter (sqlparameter[) array type) </param>
<returns>command return value (number of rows affected) </returns>
public int ExecuteNonQuery (string connstring, CommandType cmdtype, String cmdtext, params sqlparameter[] cmdparms)
{
SqlCommand cmd = new SqlCommand ();
using (SqlConnection conn = new SqlConnection (connstring))
{
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, cmdparms);
int val = cmd. ExecuteNonQuery ();
Cmd. Parameters.clear ();
return Val;
}
}
<summary>
Executes an SQL Command (using implied connectstring)
</summary>
<param name= "Cmdtype" >command type </param>
<param name= "Cmdtext" >command statement (SQL statement) </param>
<param name= "cmdparms" >command parameter (sqlparameter[) array type) </param>
<returns>command return value (number of rows affected) </returns>
public int ExecuteNonQuery (CommandType cmdtype, String cmdtext, params sqlparameter[] cmdparms)
{
SqlCommand cmd = new SqlCommand ();
using (SqlConnection conn = new SqlConnection (Constr))
{
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, cmdparms);
int val = cmd. ExecuteNonQuery ();
Cmd. Parameters.clear ();
return Val;
}
}
public static int ExecuteNonQuery (string cmdtext)
// {
// }
<summary>
Execute a simple query, just enter the SQL statement, typically used to update or delete
</summary>
<param name= "SQLText" ></param>
<returns></returns>
public int ExecuteNonQuery (string sqltext)
{
Return ExecuteNonQuery (Commandtype.text,sqltext);
}
<summary>
Executes an SQL Command (using sqltransaction)
</summary>
<param name= "Trans" > Use of sqltransaction</param>
<param name= "Cmdtype" >command type </param>
<param name= "Cmdtext" >command statement (SQL statement) </param>
<param name= "cmdparms" >command parameter (sqlparameter[) array type) </param>
<returns>command return value (number of rows affected) </returns>
public int ExecuteNonQuery (SqlTransaction trans, CommandType cmdtype,string Cmdtext, params sqlparameter[] cmdparms)
{
SqlCommand cmd = new SqlCommand ();
PrepareCommand (cmd, trans.) Connection, trans, Cmdtype, Cmdtext, cmdparms);
int val = cmd. ExecuteNonQuery ();
Cmd. Parameters.clear ();
return Val;
}
<summary>
Automatically generates CMD to perform the update of the dataset according to the specified dscommandtype type
</summary>
<param name= "connstring" >connectstring (SQL connection string) </param>
<param name= "Cmdtype" >command type </param>
<param name= "Dscommandtype" >enum type </param>
<param name= "Cmdtext" >command statement (SQL statement) </param>
<param name= "DataSet" >dataset</param>
<param name= "tablename" > table name </param>
<param name= "cmdparms" >command parameter (sqlparameter[) array type) </param>
<returns> Update Success </returns>
public bool ExecuteNonQuery (string Connstring,commandtype cmdtype,commandenum.dscommandtype dscommandtype,string Cmdtext,dataset dataset,string tablename,params sqlparameter[] cmdparms)
{
SqlDataAdapter dscommand = new SqlDataAdapter ();
SqlCommand cmd = new SqlCommand ();
using (SqlConnection conn = new SqlConnection (connstring))
{
IF (Conn. State!= ConnectionState.Open)
Conn. Open ();
Cmd. Connection = conn;
Cmd.commandtext = Cmdtext;
Cmd.commandtype = Cmdtype;
if (cmdparms!= null)
{
foreach (SqlParameter parm in cmdparms)
Cmd. Parameters.Add (Parm);
}
Switch (dscommandtype)
{
Case CommandEnum.DsCommandType.InsertCommand:
Dscommand.insertcommand = cmd;
Break
Case CommandEnum.DsCommandType.UpdateCommand:
Dscommand.updatecommand = cmd;
Break
Case CommandEnum.DsCommandType.DeleteCommand:
Dscommand.deletecommand = cmd;
Break
Default:break;
}
Dscommand.update (Dataset,tablename);
if (DataSet). HasErrors)
{
DataSet. Tables[tablename]. GetErrors () [0]. Clearerrors ();
return false;
}
Else
{
DataSet. AcceptChanges ();
return true;
}
}
}
<summary>
Update one recordset (using connstring)
</summary>
<param name= "connstring" >connectstring (SQL connection string) </param>
<param name= "Cmdinserttype" >commandinsert type </param>
<param name= "Cmdinserttext" >sql statement (Insert) </param>
<param name= "Cmdupdatetype" >commandupdate type </param>
<param name= "Cmdupdatetext" >sql statement (Update) </param>
<param name= "Cmdinserttype" >commanddelete type </param>
<param name= "Cmddeletetext" >sql statement (Delete) </param>
<param name= "cmdinsertparms" >insertcommand parameters </param>
<param name= "cmdupdateparms" >updatecommand parameters </param>
<param name= "cmddeleteparms" >deletecommand parameters </param>
<param name= "DataSet" >dataset</param>
<param name= "tablename" > table name </param>
<returns> Update Success </returns>
public bool UpdateDataSet (string Connstring,commandtype cmdinserttype,string cmdinserttext,commandtype CmdUpdateType , string Cmdupdatetext,commandtype cmddeletetype,string cmddeletetext,sqlparameter[] cmdinsertparms,sqlparameter[] Cmdupdateparms,sqlparameter[] Cmddeleteparms,dataset dataset,string tablename)
{
SqlDataAdapter dscommand = new SqlDataAdapter ();
using (SqlConnection conn = new SqlConnection (connstring))
{
IF (Conn. State!= ConnectionState.Open)
Conn. Open ();
if (Cmdinserttext!= String.Empty)
{
SqlCommand Cmdinsert = new SqlCommand ();
Cmdinsert.connection = conn;
Cmdinsert.commandtext = Cmdinserttext;
Cmdinsert.commandtype = Cmdinserttype;
if (cmdinsertparms!= null)
{
foreach (SqlParameter parm in cmdinsertparms)
CMDINSERT.PARAMETERS.ADD (Parm);
}
Dscommand.insertcommand = Cmdinsert;
}
if (Cmdupdatetext!= String.Empty)
{
SqlCommand cmdupdate = new SqlCommand ();
Cmdupdate.connection = conn;
Cmdupdate.commandtext = Cmdupdatetext;
Cmdupdate.commandtype = Cmdupdatetype;
if (cmdupdateparms!= null)
{
foreach (SqlParameter parm in cmdupdateparms)
CMDUPDATE.PARAMETERS.ADD (Parm);
}
Dscommand.updatecommand = cmdupdate;
}
if (Cmddeletetext!= String.Empty)
{
SqlCommand cmddelete = new SqlCommand ();
Cmddelete.connection = conn;
Cmddelete.commandtext = Cmddeletetext;
Cmddelete.commandtype = Cmddeletetype;
if (cmddeleteparms!= null)
{
foreach (SqlParameter parm in cmddeleteparms)
CMDDELETE.PARAMETERS.ADD (Parm);
}
Dscommand.deletecommand = Cmddelete;
}
if (Cmdinserttext = String.Empty && cmdupdatetext = String.Empty && cmddeletetext = String.Empty)
{
SqlCommandBuilder SCB = new SqlCommandBuilder (Dscommand);
return false;
}
Dscommand.update (Dataset,tablename);
if (DataSet). HasErrors)
{
DataSet. Tables[tablename]. GetErrors () [0]. Clearerrors ();
return false;
}
Else
{
DataSet. AcceptChanges ();
return true;
}
}
}
#endregion
#region ExecuteReader
<summary>
Get a SqlDataReader (using connstring)
</summary>
<param name= "ConnString" >ConnectString</param>
<param name= "Cmdtype" > Type </param>
<param name= "Cmdtext" >command statement (SELECT statement) </param>
<param name= "cmdparms" >command parameters </param>
The sqldatareader</returns> required by <returns>
Public SqlDataReader ExecuteReader (string connstring, CommandType cmdtype, String cmdtext, params sqlparameter[] Cmdparms)
{
SqlCommand cmd = new SqlCommand ();
SqlConnection conn = new SqlConnection (connstring);
Try
{
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, cmdparms);
SqlDataReader rdr = cmd. ExecuteReader (commandbehavior.closeconnection);
Cmd. Parameters.clear ();
return RDR;
}
Catch
{
Conn. Close ();
Throw
}
}
<summary>
Gets a SqlDataReader (using connstring), using the default connectionstring
</summary>
<param name= "Cmdtype" > Type </param>
<param name= "Cmdtext" >command statement (SELECT statement) </param>
<param name= "cmdparms" >command parameters </param>
<returns>SqlDataReader</returns>
Public SqlDataReader ExecuteReader (CommandType cmdtype, String cmdtext, params sqlparameter[] cmdparms)
{
SqlCommand cmd = new SqlCommand ();
SqlConnection conn = new SqlConnection (CONSTR);
Try
{
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, cmdparms);
SqlDataReader rdr = cmd. ExecuteReader (commandbehavior.closeconnection);
Cmd. Parameters.clear ();
return RDR;
}
Catch
{
Conn. Close ();
Throw
}
}
<summary>
Gets a SqlDataReader, using the default connectionstring
</summary>
<param name= "cmdtxt" > Statement command </param>
<returns></returns>
Public SqlDataReader ExecuteReader (string cmdtxt)
{
SqlCommand cmd = new SqlCommand ();
SqlConnection conn = new SqlConnection (CONSTR);
Try
{
Cmd=new SqlCommand (Cmdtxt,conn);
Conn. Open ();
SqlDataReader rdr = cmd. ExecuteReader (commandbehavior.closeconnection);
return RDR;
}
Catch
{
Conn. Close ();
Throw
}
}
#endregion
#region Private Function
<summary>
Prepare a command (using sqlparameter[] array)
</summary>
private void PrepareCommand (SqlCommand cmd, SqlConnection Conn, SqlTransaction Trans, CommandType Cmdtype, String Cmdtext , sqlparameter[] cmdparms)
{
IF (Conn. State!= ConnectionState.Open)
{
Try
{
Conn. Open ();
}
catch (Exception Ex)
{
Throw Ex;
String a = Ex.tostring ();
Return
}
}
Cmd. Connection = conn;
Cmd.commandtext = Cmdtext;
if (trans!= null)
Cmd. Transaction = trans;
Cmd.commandtype = Cmdtype;
if (cmdparms!= null)
{
foreach (SqlParameter parm in cmdparms)
Cmd. Parameters.Add (Parm);
}
}
private void PrepareCommand (SqlCommand cmd, SqlConnection Conn, SqlTransaction Trans, CommandType Cmdtype, String Cmdtext , SqlParameterCollection cmdparms)
{
IF (Conn. State!= ConnectionState.Open)
Conn. Open ();
Cmd. Connection = conn;
Cmd.commandtext = Cmdtext;
if (trans!= null)
Cmd. Transaction = trans;
Cmd.commandtype = Cmdtype;
if (cmdparms!= null)
{
foreach (SqlParameter parm in cmdparms)
Cmd. Parameters.Add (Parm);
}
}
<summary>
Add a param with the name of the field
</summary>
<param name= "FLD" ></param>
<returns></returns>
Private SqlParameter Newfieldparam (string fld)
{
SqlParameter param = new SqlParameter ();
Param. ParameterName = "@" + fld;
Param. SourceColumn = FLD;
return param;
}
<summary>
Determines whether a character is in a collection
</summary>
<param name= "str" ></param>
<param name= "Excludefields" ></param>
<returns></returns>
private bool Incolleciton (string str,ilist excludefields)
{
foreach (string s in Excludefields)
{
if (S.toupper () ==str. ToUpper ())
return true;
}
return false;
}
#endregion
#region Fill DataSet
<summary>
Populating data in a dataset (no connstring)
</summary>
<param name= "Cmdtype" > Type </param>
<param name= "Cmdtext" >command statement </param>
<param name= "tablename" > table name </param>
<param name= "cmdparms" >command parameters </param>
public void Filldata (CommandType cmdtype,string cmdtext,dataset dataset,string tablename,params SqlParameter[] Cmdparms)
{
SqlDataAdapter dscommand = new SqlDataAdapter ();
SqlCommand cmd = new SqlCommand ();
Dscommand.selectcommand = cmd;
DSCOMMAND.TABLEMAPPINGS.ADD ("Table", TableName);
using (SqlConnection conn = new SqlConnection (Constr))
{
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, cmdparms);
Dscommand.fill (Dataset,tablename);
}
}
<summary>
Populating data into a dataset (using ConnString + sqlparametercollection)
</summary>
<param name= "ConnString" >ConnectString</param>
<param name= "Cmdtype" > Type </param>
<param name= "Cmdtext" >command statement </param>
<param name= "tablename" > table name </param>
<param name= "cmdparms" >command parameters (sqlparametercollection) </param>
public void Filldataex (string connstring, CommandType cmdtype,string cmdtext,dataset dataset,string TableName, SqlParameterCollection cmdparms)
{
SqlDataAdapter dscommand = new SqlDataAdapter ();
SqlCommand cmd = new SqlCommand ();
Dscommand.selectcommand = cmd;
DSCOMMAND.TABLEMAPPINGS.ADD ("Table", TableName);
using (SqlConnection conn = new SqlConnection (connstring))
{
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, cmdparms);
Dscommand.fill (DataSet);
}
}
#endregion
Internal string constr= null;//= "Uid =sa; Pwd=sa; Server = Drago;database =northwind ";
Internal SqlConnection MCN = new SqlConnection ();
Internal DataSet m_dataset =new System.Data.DataSet ();
}
}
