|
|
Sql Code skeleton for ADO.NET via System.Data.SqlClient
|
|
|
Select the type of call you want to make, Text or stored procedure, and how
you expect to get data back. The skeleton code for that combination will be generated below. From there you can cut and paste as a starting point. If there are other access technologies that you'd like to see let me know. |
| Code For This Operation |
|---|
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = "connection string";
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
/*
* Build and call a SQL statement
*/
cmd.CommandText = "select,update,insert,delete";
cmd.CommandType = CommandType.Text;
/*
* Call a Stored Procedure
*/
cmd.CommandText = "name_of_procecure";
cmd.CommandType = CommandType.StoredProcedure;
/*
* Set up two parameters, and integer and string
*/
SqlParameter param = new SqlParameter("@intparam", SqlDbType.Int);
param.Value = 46;
cmd.Parameters.Add(param);
param = new SqlParameter("@strparam", SqlDbType.VarChar);
param.Value = "string parameter";
cmd.Parameters.Add(param);
/*
* No results, do SQL
*/
cmd.ExecuteNonQuery();
/*
* Get a scaler result
*/
object oResult = cmd.ExecuteScalar();
/*
* We will be receiving a value from a RETURN statement
*/
SqlParameter retParam = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
retParam.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(retParam);
/*
* Using a RETURN value, use nonQuery
*/
cmd.ExecuteNonQuery();
int retval = Convert.ToInt32(retParam.Value);
/*
* Get a SqlDataReader and read records
*/
SqlDataReader rdr = cmd.ExecuteReader();
/*
* Read until no more records
*/
while (rdr.Read())
{
string value = (string) rdr["Columnname"];
}
/*
* Fill a DataSet
*/
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
/*
* Read all the rows in table 0
*/
foreach (DataRow dr in ds.Tables[0].Rows) {
string value = dr["Columnname"];
}
/*
* Finished, Close connection
*/
cnn.Close();
|
|
1 responses
| | 
|