Database Operation

 [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();
        }