CODEWITHSUNDEEP
c#dataset
yihang hwang
yihang hwang

Reputation: 419

How to pass parameter to Dataset for query using c#

I want to change SQL statement in DataSet for adding where query

where LogTime between @parameter1 and @parameter2

how can I pass parameter DateStr and DateEnd to @parameter1 and @parameter2

from c# method 

DataSet1 ds = new DataSet1();

I guess We should Using DataTable1TableAdapter from DataSet1

Is any suggested way to Implement it ?

We got these query in DataTable1TableAdapter already

SELECT IPassLog.ParkID, ParkInfo.ParkName, IPassLog.UserID, IPassLog.LogTime, IPassLog.LogInTime, IPassLog.InitBalance,
                IPassLog.TranAmount, IPassLog.TranBalance, IPassLog.CheckDone, IPassLog.TranSN, IPassLog.PackName, IPassLog.ResultName, IPassLog.SettlementDate,
                IPass_Result_Hex.Result_Desc, IPass_ErrorCode.Explanation
                FROM IPassLog INNER JOIN ParkInfo ON IPassLog.ParkID = ParkInfo.ParkID
                FULL JOIN IPass_Result_Hex ON IPassLog.VerifyResult = IPass_Result_Hex.Result_Hex FULL JOIN IPass_ErrorCode ON IPassLog.VerifyFlag = IPass_ErrorCode.Code;

and I want add where statement into these query

Upvotes: 0

Views: 6712

Answers (2)

Pranay Rana
Pranay Rana

Reputation: 176946

follow the post : Tutorial 1: Creating a Data Access Layer which talks about how to deal with parameter in table adapter query. it also provide code example for the same.

if you can to pass parameter to your query then create parameter like this 

  SqlParameter param= new SqlParameter();
   param.ParameterName = "@parameter1";// Defining Name
   param.SqlDbType = SqlDbType.Int; // Defining DataType
   param.Direction = ParameterDirection.Input; // Setting the direction 
   param.Value = inputvalue;

and add created parameter to your command object 

 // Adding Parameter instances to sqlcommand
   comm.Parameters.Add(param);

Upvotes: 1

sujith karivelil
sujith karivelil

Reputation: 29036

You can make use of the SqlDataAdapter class's constructor that accept SqlCommand as parameter. So that you can pass the parameterized command to this class to create the instance of SqlDataAdapter I prefer you to use like thi:

string connectionString = "your connection string here";
string query = "select blah, foo from mytable where LogTime >= @parameter1 and LogTime<= @parameter2";
DataSet ds = new DataSet();
using (SqlCommand cmd = new SqlCommand(query, new SqlConnection(connectionString)))
{
    cmd.Parameters.Add("parameter1", SqlDbType.DateTime).Value = dateInput1;
    cmd.Parameters.Add("parameter2", SqlDbType.DateTime).Value = dateInput2;
    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) {
        adapter.Fill(ds);
    }
}

Upvotes: 3

Related Questions