Application of stored procedures and DataTable in C #

Stored procedure p_operatordetails, there are four parameters @sdatetime, @eDatetime, @operatorNo, @transdesc. Where @operatorno and @transdesc are two optional parameters, through the four parameters how to extract their own specific data fields from the stored procedure, the method is different, here I use a DataTable related operations.

First open the database link

String Strcon = system.configuration.configurationmanager.appsettings["ConnectionString"]. ToString ();
SqlConnection conn = new SqlConnection (Strcon);
Conn. Open ();

Create a DataTable Object

DataTable dt = new DataTable ();

Create a Sqldataadapt object to manipulate the data source stored procedure P_operatordetails

SqlDataAdapter Da=new SqlDataAdapter ("P_operatordetails", conn);
Da.SelectCommand.CommandType = CommandType.StoredProcedure;

To create a SQL parameter and assign it a value

SqlParameter p1 = new SqlParameter ("@sDatetime", sqldbtype.datetime);
SqlParameter P2 = new SqlParameter ("@eDatetime", sqldbtype.datetime);
SqlParameter p3 = new SqlParameter ("@operatorNo", SqlDbType.Int);
SqlParameter P4 = new SqlParameter ("@transdesc", SqlDbType.VarChar);

P1. Value = Stransactiondatestart;
P2. Value = Stransactiondateend;
if (Soperatorno! = "")
{
P3. Value = Convert.ToInt32 (Soperatorno);
Da. SELECTCOMMAND.PARAMETERS.ADD (p3);
}
Else
{
P3. Value = "";
}

if (Stranstypeno! = "")
{
P4. Value = Stranstypeno;
Da. SELECTCOMMAND.PARAMETERS.ADD (p4);
}
Else
{
P4. Value = "";
}

Da. SelectCommand.Parameters.Add (p1);
Da. SELECTCOMMAND.PARAMETERS.ADD (p2);

Populate the DataTable with the resulting data set through the SqlDataAdapter object

Da. Fill (DT);

Note that the resulting DataTable object is not able to do SQL operations (maybe I do the project with this method does not apply to you), the key problem comes, I want this dataset inside the feature field rather than the entire stored procedure to get the data set, The method I use is to use the Clone method of a DataTable to provide data for use in my reports, possibly a new virtual table.

DataTable NEWDT = new DataTable ();
NEWDT = dt. Clone ();

Data in a DataTable cannot manipulate data directly like an actual data table, but it has its own approach

NEWDT = dt. Defaultview.totable (False, new string[] {"Bankcardid", "EmployeeName", "Deptname", "Transdesc", "Devicedatetime" , "remain", "operatorname"});

At this point, I want the specific field of the virtual table will come out (report, for a few days to find this method is still the most appropriate, do not know that you have a better way).

Application of stored procedures and DataTable in C #