MySQL database category

Using System; using System. Data;
Using System. Data. SqlClient;
Using System. Configuration;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Web. UI. HtmlControls;

/// <Summary>
/// Summary of Execommd
/// </Summary>
Public class Execommd
{
Private string _ connectionString;
SqlConnection SqlConnectionsifang = new SqlConnection ();
SqlCommand SqlCommandsifang = new SqlCommand ();
Public Execommd ()
{
//
// TODO: add the constructor logic here
//
_ ConnectionString = ConfigurationManager. ConnectionStrings ["testConnectionString"]. ConnectionString; // defines the data connection string
SqlConnectionsifang. ConnectionString = _ connectionString; // initialize the data connection
SqlCommandsifang. Connection = SqlConnectionsifang; // initialize SqlCommand
}
//
// Execommdnone is used to execute all SQL statements without return types.
Public void Execommdnone (string sqlstr)
{
Try
{
If (SqlConnectionsifang. State. ToString ()! = "Open ")
{
SqlConnectionsifang. Open ();
SqlCommandsifang. CommandText = sqlstr;
SqlCommandsifang. ExecuteNonQuery ();
}
}
Catch (SqlException e)
{
Throw e;
}
Finally
{
If (SqlConnectionsifang. State. ToString ()! = "Close ")
{
SqlConnectionsifang. Close ();
}
}
}
// Execommdint is used to execute all SQL statements, and the number of affected rows is returned.
Public int Execommdint (string sqlstr)
{
Int num = 0;
Try
{
If (SqlConnectionsifang. State. ToString ()! = "Open ")
{
SqlConnectionsifang. Open ();
SqlCommandsifang. CommandText = sqlstr;
Num = SqlCommandsifang. ExecuteNonQuery ();
}
}
Catch (SqlException e)
{
Throw e;
}
Finally
{
If (SqlConnectionsifang. State. ToString ()! = "Close ")
{
SqlConnectionsifang. Close ();
}
}
Return num;

}

//

// Execommdcount is used to execute all SQL statements and returns the first column of the first line of the int type.

Public int Execommdcount (string sqlstr)
{
Int num = 0;
Try
{
If (SqlConnectionsifang. State. ToString ()! = "Open ")
{
SqlConnectionsifang. Open ();
SqlCommandsifang. CommandText = sqlstr;
Num = (int) SqlCommandsifang. ExecuteScalar ();
}
}
Catch (SqlException e)
{
Throw e;
}
Finally
{
If (SqlConnectionsifang. State. ToString ()! = "Close ")
{
SqlConnectionsifang. Close ();
}
}
Return num;
}
// Execommdstring is used to execute all SQL statements that return the first column of the string type.
Public string Execommdstring (string sqlstr)
{
String result = "";
Try
{
If (SqlConnectionsifang. State. ToString ()! = "Open ")
{
SqlConnectionsifang. Open ();
SqlCommandsifang. CommandText = sqlstr;
Result = SqlCommandsifang. ExecuteScalar (). ToString ();
}
}
Catch (Exception e)
{
Throw e;
}
Finally
{
If (SqlConnectionsifang. State. ToString ()! = "Close ")
{
SqlConnectionsifang. Close ();
}
}
Return result;
}
 
// Execommddatatable is used to return datatable based on parameters
Public DataTable Execommddatatable (string sqlstr)
{
SqlDataAdapter da = new SqlDataAdapter (sqlstr, SqlConnectionsifang );
DataSet ds = new DataSet ();
Try
{
SqlConnectionsifang. Open ();
Da. Fill (ds, "tbl ");
}
Catch
{
}
Finally
{
SqlConnectionsifang. Close ();
}
Return ds. Tables ["tbl"];

}

//

// Execommddataset is used to return dataset based on parameters.
Public DataSet Execommddataset (string sqlstr)
{
SqlDataAdapter da = new SqlDataAdapter (sqlstr, SqlConnectionsifang );
DataSet ds = new DataSet ();
Try
{
SqlConnectionsifang. Open ();
Da. Fill (ds );
}
Catch
{
}
Finally
{
SqlConnectionsifang. Close ();
}
Return ds;
}
// ExecommdReader is used to return the ExecommdReader according to the parameter

Public SqlDataReader ExecommdReader (string sqlstr)
{
SqlCommand cm = new SqlCommand (sqlstr, SqlConnectionsifang );
If (cm. Connection. State. ToString ()! = "Open ")
Cm. Connection. Open ();
SqlDataReader sdr = cm. ExecuteReader ();
Return sdr;

}
 
/// <Summary>
/// SQL statement with substitute parameters for insert and update
/// </Summary>
/// <Param name = "count"> Number of values </param>
/// <Param name = "values"> the three parameters are "SQL statement", parameter name link string, and parameter type (in the database) link string, parameter content </param>
/// <Returns> </returns>
Public bool ExecommdParams (int count, params string [] values)
{
Try
{
String sqlstr = "";
String ParamsStr = "";
String ParamsType = "";
Int valueLenght = count-3;
String [] count_str_len = new string [valueLenght];
For (int I = 0, j = 0; I <count; I ++)
{
If (I = 0) // SQL statement
{
Sqlstr = values [I];
}
Else if (I = 1) // parameter name string
{
ParamsStr = values [I];
}
Else if (I = 2) // parameter type string
{
ParamsType = values [I];
}
Else
{
Count_str_len [j] = values [I]; // parameter content string
}

}

String [] pamstr = ParamsStr. Split (','); // storage parameter name
String [] pamtype = ParamsType. Split (','); // storage parameter database type

SqlCommandsifang. CommandText = sqlstr;

Int pamstrlen = pamstr. Length; // number of parameters
Int pamtypelen = pamtype. Length; // Number of parameter types
If (pamstrlen = valueLenght) & (valueLenght = pamtypelen) // number of parameters, number of parameter types, and same number of content
{
For (int I = 0; I <valueLenght; I ++)
{
SqlCommandsifang. Parameters. AddWithValue (pamstr [I], pamtype [I]). Value = count_str_len [I];
}

SqlCommandsifang. Connection. Open ();
Int rowsAffected = SqlCommandsifang. ExecuteNonQuery ();
SqlConnectionsifang. Close ();
SqlCommandsifang. Dispose ();

If (rowsAffected> 0)
Return true;
Else return false;

}
Else
{
Return false;
}

}
Catch (Exception ex)
{
Throw ex;
}

}

/// <Summary>
/// Execute the stored procedure without Parameters
/// </Summary>
/// <Param name = "ProName"> stored procedure name </param>
/// <Returns> </returns>
Public DataSet ExecommdProcedure (string ProName)
{
SqlCommandsifang. CommandType = CommandType. StoredProcedure; // specifies the operation for executing the Stored Procedure
SqlCommandsifang. CommandText = ProName; // stored procedure name
SqlDataAdapter adapter = new SqlDataAdapter (SqlCommandsifang );
DataSet ds;
Adapter. Fill (ds );
Return ds;
}

// Execute the stored procedure with Parameters
Public SqlDataAdapter ExecommdProcedureParameter (int count, string ProName, params string [] values)
{
// Values format
// "@ Id, @ name, @ sex" "Char, DateTime, Float" "10, 5, 20"


SqlCommandsifang. CommandType = CommandType. StoredProcedure; // specifies the operation for executing the Stored Procedure
SqlCommandsifang. CommandText = ProName; // stored procedure name

String ParamsStr = "";
String ParamsType = "";
String ParamsValue = "";
Int valueLenght = count-3;
String [] count_str_len = new string [valueLenght];
For (int I = 0, j = 0; I <count; I ++)
{
If (I = 0) // parameter string
{
ParamsStr = values [I];
}
Else if (I = 1) // parameter type string
{
ParamsType = values [I];
}
Else if (I = 2) // parameter numeric string
{
ParamsValue = values [I];
}
Else
{
Count_str_len [j] = values [I]; // parameter content
}

}

String [] pamstr = ParamsStr. Split (','); // store each parameter
String [] pamtype = ParamsType. Split (','); // store each parameter database type
String [] pamValue = ParamsValue. Split (','); // store each parameter database type

Int pamstrlen = pamstr. Length; // number of parameters
Int pamtypelen = pamtype. Length; // Number of parameter types
Int pamValuelen = pamValue. Length;

If (pamstrlen = valueLenght & pamtypelen = valueLenght & pamValuelen = valueLenght) & (pamstrlen = pamtypelen & pamtypelen = pamValuelen & pamstrlen = pamValuelen ))
{
For (int I = 0; I <valueLenght; I ++)
{
// SqlParameter parid = new SqlParameter (pamstr [0], pamtype [0]. ToString (), Convert. ToInt32 (pamValue [0]);
SqlParameter parid = new SqlParameter ();
Parid. ParameterName = pamstr [I];
{
If (pamtype [I] = "Bit ")
Parid. SqlDbType = SqlDbType. Bit;
Else if (pamtype [I] = "Char ")
Parid. SqlDbType = SqlDbType. Char;
Else if (pamtype [I] = "DateTime ")
Parid. SqlDbType = SqlDbType. DateTime;
Else if (pamtype [I] = "Float ")
Parid. SqlDbType = SqlDbType. Float;
Else if (pamtype [I] = "Int ")
Parid. SqlDbType = SqlDbType. Int;
Else if (pamtype [I] = "NVarChar ")
Parid. SqlDbType = SqlDbType. NVarChar;
Else if (pamtype [I] = "Text ")
Parid. SqlDbType = SqlDbType. Text;
Else if (pamtype [I] = "VarChar ")
Parid. SqlDbType = SqlDbType. VarChar;
Else if (pamtype [I] = "NText ")
Parid. SqlDbType = SqlDbType. NText;
}
If (pamValue [I]! = "")
Parid. Size = Convert. ToInt32 (pamValue [I]);
Parid. Value = count_str_len [I];
SqlCommandsifang. Parameters. Add (parid );
}

}
SqlDataAdapter adapter = new SqlDataAdapter (SqlCommandsifang );
Return adapter;

}

}