в 15:31, 26 мая 2010

2010-05-26T15:31:53Z

Admin: 1 версия

2010-05-26T12:19:10Z

1 версия

Новая страница

==Add SqlParameter to SqlCommand==

<source lang="csharp">using System;
using System.Drawing;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
public class MainClass
{
[STAThread]
static void Main()
{
string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
using ( SqlConnection conn = new SqlConnection( cstr ) )
{
conn.Open();
string selstr = "select FirstName from Employee where lastname = @name";
SqlCommand cmd = new SqlCommand( selstr, conn );
SqlParameter name = cmd.Parameters.Add( "@name", SqlDbType.NVarChar, 15 );
name.Value = "Tang";
SqlDataReader rdr = cmd.ExecuteReader();
if ( rdr.Read() )
{
Console.WriteLine(rdr.GetString( 0 ) );
}
else
{
Console.WriteLine("not available yet" );
}
}
}
}</source>

==Command Parameter==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class CommandParameters
{
static void Main()
{
string fname = "Z";
string lname = "Z";
SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind");
string sqlqry = @"select count(*) from employees";
string sqlins = @"insert into employees(firstname,lastname)values(@fname, @lname)";
string sqldel = @"delete from employees where firstname = @fname and lastname = @lname";
SqlCommand cmdqry = new SqlCommand(sqlqry, conn);
SqlCommand cmdnon = new SqlCommand(sqlins, conn);
cmdnon.Prepare();
cmdnon.Parameters.Add("@fname", SqlDbType.NVarChar, 10);
cmdnon.Parameters.Add("@lname", SqlDbType.NVarChar, 20);

try
{
conn.Open();
Console.WriteLine("Before INSERT: Number of employees {0}\n", cmdqry.ExecuteScalar());
cmdnon.Parameters["@fname"].Value = fname;
cmdnon.Parameters["@lname"].Value = lname;
Console.WriteLine("Executing statement {0}", cmdnon.rumandText);
cmdnon.ExecuteNonQuery();
Console.WriteLine("After INSERT: Number of employees {0}\n", cmdqry.ExecuteScalar());
cmdnon.rumandText = sqldel;
Console.WriteLine("Executing statement {0}", cmdnon.rumandText);
cmdnon.ExecuteNonQuery();
Console.WriteLine("After DELETE: Number of employees {0}\n", cmdqry.ExecuteScalar());
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
Console.WriteLine("Connection Closed.");
}
}
}</source>

==Passing a Null Value to a Query Parameter==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
using (SqlConnection connection = new SqlConnection(sqlConnectString))
{
SqlCommand command =new SqlCommand("PassNullParameter", connection);
command.rumandType = CommandType.StoredProcedure;
command.Parameters.Add("@ValueIn", SqlDbType.Int);
command.Parameters[0].Value = 1;
connection.Open( );
Console.WriteLine(command.ExecuteScalar( ));
command.Parameters[0].Value = null;
Console.WriteLine("Parameter value = null");
try{

Console.WriteLine(Convert.ToBoolean(command.ExecuteScalar( )));
}catch (Exception ex){
Console.WriteLine("ERROR: {0}", ex.Message);
}
command.Parameters[0].Value = System.DBNull.Value;
Console.WriteLine(command.ExecuteScalar( ));
}
}
}</source>

==Pass value to SqlCommand with SqlParameter==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
public static void Main()
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = @"Data Source = .\sqlexpress;Database = Northwind; Integrated Security=SSPI";
con.Open();

string employeeID = "5";
string title = "Cleaner";

using (SqlCommand com = con.CreateCommand())
{
com.rumandType = CommandType.Text;
com.rumandText = "UPDATE Employee SET Title = @title" +
" WHERE Id = @Employeeid";

// Create a SqlParameter object for the title parameter.
SqlParameter p1 = com.CreateParameter();
p1.ParameterName = "@title";
p1.SqlDbType = SqlDbType.VarChar;
p1.Value = title;
com.Parameters.Add(p1);

// Use a shorthand syntax to add the id parameter.
com.Parameters.Add("@Employeeid", SqlDbType.Int).Value = employeeID;

// Execute the command and process the result.
int result = com.ExecuteNonQuery();

if (result == 1)
{
Console.WriteLine("Employee {0} title updated to {1}.",
employeeID, title);
}
else
{
Console.WriteLine("Employee {0} title not updated.",
employeeID);
}
}
}
}
}</source>

← Предыдущая	Версия 15:31, 26 мая 2010
(нет различий)

Csharp/CSharp Tutorial/ADO.Net/SqlParameter - История изменений

в 15:31, 26 мая 2010

Admin: 1 версия