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