[Simple Connection With Local & Windows Authenticationi]
String strConn = "Server = (local);" + "integrated security = SSPI;" + "database = factory"; SqlConnection conn = new SqlConnection(strConn); try { conn.Open(); } catch (Exception e) { System.Console.WriteLine(e.ToString()); } finally { conn.Close(); }
[Connetion String with UserId and Password]
String strConn = "Persist Security Info=False;" + "User ID=Rich;" + "Password=1984;" + "Initial Catalog=factory;";
[Connetion String with Database Server's IP]
String strConn = "Server =192.168.1.101;"+ "Persist Security Info=False;" + "User ID=Rich;" + "Password=1984;" + "Initial Catalog=factory;";
[Create Connection Using Web.config]
// in Web.config /*
<configuration>
<appSettings/>
<connectionStrings>
<add name ="Factory" providerName="System.Data.SqlClient" connectionString="Server = (local);integrated security =SSPI;database =factory"/>
</connectionStrings>*/ // encrypt at console >aspnet_regiis -pef "connectionStrings" F:/MyStudy/Dragon // // in .CS file ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["Factory"]; DbProviderFactory pf = DbProviderFactories.GetFactory(settings.ProviderName); DbConnection conn = pf.CreateConnection(); conn.ConnectionString = settings.ConnectionString; // // ConnectionStringSettings stringSettings = ConfigurationManager.ConnectionStrings["Factory"]; // SqlConnection conn = new SqlConnection(stringSettings.ConnectionString); //
[Use Transaction Code]
_conn.Open();SqlTransaction tran = _conn.BeginTransaction(); String strSql = "UPDATE [USER] SET password =/'xsd/' WHERE username = /'test/';"; SqlCommand cmd = new SqlCommand(strSql,_conn); cmd.Transaction = tran; int result = cmd.ExecuteNonQuery(); tran.Rollback(); //tran.Commit();
[Use Store Procedure code]
SqlCommand cmd = new SqlCommand("ASimpleProcedure", _conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 20)); cmd.Parameters.Add(new SqlParameter("@age", SqlDbType.Int, 20)); cmd.Parameters["@username"].Value = "test"; cmd.Parameters["@age"].Value = 24; try { _conn.Open(); cmd.UpdatedRowSource = UpdateRowSource.OutputParameters; cmd.ExecuteNonQuery(); }