SQLServer2000 & C# - Code

I hope that these codes help you for connect to SQLServer2000 (with SP3) by C#.

این ها چند تا تیکه کد برای دسترسی به پایگاه های داده مبتنی بر 2000 SQLServer از طریق C# و dotNetFrameWork1.1 هستن.
راستش مجبور شدم برای یه کار عجله ای 1 روزه (که 2 روز طول کشید) بدون کتاب SQLServer، StoredProcedure رو مطالعه کنم و روش های مختلف ارتباط مابین این DBMS و #C رو پیدا کنم. (این یعنی اگه DB کارنکردین نگران نباشین سخت که نیست هیچ ...)
این تیکه کدها در انتها خیلی روند کاری رو تسریع کردن ممکنه به درد بخوره گرچه می شه هر جایی به راحتی پیداشون کرد مثل MSDN.

<Binding SQL Data to a DataGrid>
SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;database=pubs;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter("select * from Authors", myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, "Authors");
MyDataGrid.DataSource=ds.Tables["Authors"].DefaultView;
MyDataGrid.DataBind();

<Binding SQL Data to a SqlDataReader>
SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;database=pubs;Trusted_Connection=yes");
SqlCommand myCommand = new SqlCommand("select * from Authors", myConnection);
myConnection.Open();
SqlDataReader dr = myCommand.ExecuteReader();
MyDataGrid.DataSource = dr;
MyDataGrid.DataBind();
myConnection.Close();

<HELP>
MyDataGrid.DataSource=ds.Tables["Authors"].DefaultView;
برابر است با (Equal with)
MyDataGrid.DataSource=ds;
MyDataGrid.DataMember="Authors";

<Performing a Parameterized Select>
String selectCmd = "select * from Authors where state = @State";
SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;database=pubs;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter(selectCmd, myConnection);
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@State", SqlDbType.NVarChar, 2));
myCommand.SelectCommand.Parameters["@State"].Value = MySelect.Value;
DataSet ds = new DataSet();
myCommand.Fill(ds, "Authors");
MyDataGrid.DataSource= ds.Tables["Authors"].DefaultView;
MyDataGrid.DataBind();

<Inserting Data in a SQL Database>
String insertCmd = "insert into Authors (au_id, au_lname, au_fname, phone, address, city, state, zip, contract) values (@Id, @LName, @FName, @Phone, @Address, @City, @State, @Zip, @Contract)";
SqlCommand myCommand = new SqlCommand(insertCmd, myConnection);
myCommand.Parameters.Add(new SqlParameter("@Id", SqlDbType.NVarChar, 11));
myCommand.Parameters["@Id"].Value = Server.HtmlEncode(au_id.Value);
myCommand.Parameters.Add(new SqlParameter("@LName", SqlDbType.NVarChar, 40));
myCommand.Parameters["@LName"].Value = Server.HtmlEncode(au_lname.Value);
myCommand.Parameters.Add(new SqlParameter("@FName", SqlDbType.NVarChar, 20));
myCommand.Parameters["@FName"].Value = Server.HtmlEncode(au_fname.Value);
myCommand.Parameters.Add(new SqlParameter("@Phone", SqlDbType.NChar, 12));
myCommand.Parameters["@Phone"].Value = Server.HtmlEncode(phone.Value);
myCommand.Parameters.Add(new SqlParameter("@Address", SqlDbType.NVarChar, 40));
myCommand.Parameters["@Address"].Value = Server.HtmlEncode(address.Value);
myCommand.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar, 20));
myCommand.Parameters["@City"].Value = Server.HtmlEncode(city.Value);
myCommand.Parameters.Add(new SqlParameter("@State", SqlDbType.NChar, 2));
myCommand.Parameters["@State"].Value = state.Value;
myCommand.Parameters.Add(new SqlParameter("@Zip", SqlDbType.NChar, 5));
myCommand.Parameters["@Zip"].Value = Server.HtmlEncode(zip.Value);
myCommand.Connection.Open();
try
{
myCommand.ExecuteNonQuery();
Message.InnerHtml = "<b>Record Added</b><br>" + insertCmd.ToString();
}
catch (SqlException e)
{
if (e.Number == 2627)
Message.InnerHtml = "ERROR: A record already exists with the same primary key";
else
Message.InnerHtml = "ERROR: Could not add record, please ensure the fields are correctly filled out";
Message.Style["color"] = "red";
}
myCommand.Connection.Close();
BindGrid();

<Working with Master-Detail Relationships>
<ASP:DataGrid id="MyDataGrid" runat="server" Width="800" BackColor="#ccccff" BorderColor="black" ShowFooter="false" CellPadding=3 CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#aaaadd" DataKeyField="au_id">
<Columns>
<asp:HyperLinkColumn DataNavigateUrlField="au_id" DataNavigateUrlFormatString="datagrid13_details.aspx?id={0}" Text="Get Details"/>
</Columns>
</ASP:DataGrid>

<Writing and Using Stored Procedures>
SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;database=northwind;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter("Ten Most Expensive Products", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
myCommand.Fill(ds, "Products");
MyDataGrid.DataSource=ds.Tables["Products"].DefaultView;
MyDataGrid.DataBind();

<Writing and Using Stored Procedures with Parameters(1)>
SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;database=northwind;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter("SalesByCategory", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@CategoryName", SqlDbType.NVarChar, 15));
myCommand.SelectCommand.Parameters["@CategoryName"].Value = SelectCategory.Value;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@OrdYear", SqlDbType.NVarChar, 4));
myCommand.SelectCommand.Parameters["@OrdYear"].Value = SelectYear.Value; DataSet ds = new DataSet();
myCommand.Fill(ds, "Sales");
MyDataGrid.DataSource=ds.Tables["Sales"].DefaultView;
MyDataGrid.DataBind();

<Writing and Using Stored Procedures with Parameters(2)>
SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;database=northwind;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter("Employee Sales By Country", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@Beginning_Date", SqlDbType.DateTime));
myCommand.SelectCommand.Parameters["@Beginning_Date"].Value = BeginDate.SelectedDate;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@Ending_Date", SqlDbType.DateTime));
myCommand.SelectCommand.Parameters["@Ending_Date"].Value = EndDate.SelectedDate; DataSet ds = new DataSet();
myCommand.Fill(ds, "Sales");
MyDataGrid.DataSource=ds.Tables["Sales"].DefaultView;
MyDataGrid.DataBind();