Beispiel #1
0
        // -------------------------------------------------------------------------
        #endregion

        #region Work With DBConnectionStringBuilder/ SqlConnectionStringBuilder
        // ------------------------ Work With DBConnectionStringBuilder/ SqlConnectionStringBuilder -------------------------

        public static void TestConnectionStringBuilder()
        {
            using (SqlConnection connection = new SqlConnection())
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                // you define DataSource property as machinename\instance.
                // if you are in your machine you can use localhost\instance.
                builder.DataSource = @"DESKTOP-0GVLH4P\SQLEXPRESS";

                // your database
                builder.InitialCatalog = "AutoLot";

                // security
                builder.IntegratedSecurity = true;

                // timeout
                builder.ConnectTimeout = 20; // default 15 seconds


                connection.ConnectionString = builder.ConnectionString;

                Console.WriteLine(DbUtilities.GetInfo(connection));

                // you can also change in ConnectionString With SqlConnectionStringBuilder
                // after getting it from your .config file or what else.

                connection.Open();

                Console.WriteLine(DbUtilities.GetInfo(connection));

                string sql = "SELECT * FROM Inventory";

                SqlCommand command = new SqlCommand(sql, connection);

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    Console.WriteLine("-------------------- Inventory --------------------");
                    Console.WriteLine($" CarId       Make        PetName");
                    Console.WriteLine("------------------------------------------");
                    while (reader.Read())
                    {
                        Console.WriteLine($" {reader["CarId"]}       {reader["Make"]}        {reader["PetName"]}");
                    }
                    Console.WriteLine("------------------------------------------");
                }

                Console.WriteLine("--------------------------- End ---------------------------");
            }
        }
Beispiel #2
0
        // -------------------------------------------------------------------------
        #endregion

        #region Working with Parameterized Command Objects
        // ------------------------ Working with Parameterized Command Objects -------------------------
        //
        // parameterized queries typically execute much faster than a literal SQL string
        // because they are parsed exactly once(rather than each time the SQL string is assigned to the CommandText
        // property). Parameterized queries also help protect against SQL injection attacks(a well-known data access
        // security issue).


        // While building a parameterized query often requires more code, the end result is a more convenient
        // way to tweak SQL statements programmatically, as well as to achieve better overall performance.They also
        // are extremely helpful when you want to trigger a stored procedure.

        // Table 21-7. Key Members of the DbParameter Type
        // ---------------------------------------------------------------------------------------------------
        // Property         Meaning in Life
        // ---------------------------------------------------------------------------------------------------
        // DbType           Gets or sets the native data type of the parameter, represented as a CLR data type
        // Direction        Gets or sets whether the parameter is input-only, output-only, bidirectional, or a return
        //                  value parameter
        // IsNullable       Gets or sets whether the parameter accepts null values
        // ParameterName    Gets or sets the name of the DbParameter
        // Size             Gets or sets the maximum parameter size of the data in bytes; this is useful only for
        //                  textual data
        // Value            Gets or sets the value of the parameter
        // ---------------------------------------------------------------------------------------------------
        public static void TestInsertWithParameters(string make, string color, string petName)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString =
                    @"Server=localhost\SQLEXPRESS;
                      Database=AutoLot;
                      Trusted_Connection=True;".Replace(" ", "").Replace(NewLine, "");

                connection.Open();

                Console.WriteLine(DbUtilities.GetInfo(connection));

                string sql = $"INSERT INTO Inventory (Make, Color, PetName) VALUES (@make, @color, @petName)";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    SqlParameter makeParameter = new SqlParameter()
                    {
                        ParameterName = "make",
                        Value         = make,
                        SqlDbType     = SqlDbType.NVarChar,
                    };

                    SqlParameter colorParameter = new SqlParameter()
                    {
                        ParameterName = "color",
                        Value         = color,
                        SqlDbType     = SqlDbType.NVarChar,
                    };

                    SqlParameter petNameParameter = new SqlParameter()
                    {
                        ParameterName = "petName",
                        Value         = petName,
                        SqlDbType     = SqlDbType.NVarChar,
                    };

                    command.Parameters.AddRange(new[] { makeParameter, colorParameter, petNameParameter });

                    command.CommandType = CommandType.Text;
                    int affectedRows = command.ExecuteNonQuery();
                }
            }
        }
Beispiel #3
0
        // ----------------------------------------------------------------------------------------------------
        #endregion

        #region Executing a Stored Procedure
        // ------------------------ Executing a Stored Procedure -------------------------
        public static string LookUpPetName(int carId)
        {
            string result;

            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString =
                    @"Server=localhost\SQLEXPRESS;
                      Database=AutoLot;
                      Trusted_Connection=True;".Replace(" ", "").Replace(NewLine, "");

                connection.Open();

                Console.WriteLine(DbUtilities.GetInfo(connection));

                using (SqlCommand command = new SqlCommand("GetPetName", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@carID",
                        Value         = carId,
                        SqlDbType     = SqlDbType.Int,
                        Direction     = ParameterDirection.Input
                    });

                    command.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@petName",
                        SqlDbType     = SqlDbType.NVarChar,
                        Size          = 50,
                        Direction     = ParameterDirection.Output
                    });

                    int affectedRows = command.ExecuteNonQuery();

                    result = (string)command.Parameters["@petName"].Value;
                    return(result);
                }
            }
        }
Beispiel #4
0
        // -------------------------------------------------------------------------
        #endregion

        #region Insert, Update, Delete Queries
        // ------------------------ Insert Query -------------------------

        public static void TestInsert(string make, string color, string petName)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString =
                    @"Server=localhost\SQLEXPRESS;
                      Database=AutoLot;
                      Trusted_Connection=True;".Replace(" ", "").Replace(NewLine, "");

                connection.Open();

                Console.WriteLine(DbUtilities.GetInfo(connection));

                string sql = $"INSERT INTO Inventory (Make, Color, PetName) VALUES ('{make}', '{color}', '{petName}')";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.CommandType = CommandType.Text;
                    int affectedRows = command.ExecuteNonQuery();
                }
            }
        }
Beispiel #5
0
        public static void TestDeleteWithParameters(int carId)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString =
                    @"Server=localhost\SQLEXPRESS;
                      Database=AutoLot;
                      Trusted_Connection=True;".Replace(" ", "").Replace(NewLine, "");

                connection.Open();

                Console.WriteLine(DbUtilities.GetInfo(connection));

                string sql = $"DELETE FROM Inventory WHERE CarId = @carId";



                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    try
                    {
                        command.Parameters.Add(new SqlParameter("carId", carId)
                        {
                            SqlDbType = SqlDbType.Int
                        });
                        command.CommandType = CommandType.Text;
                        int affectedRows = command.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        Exception error = new Exception("Sorry! That car is on order!", ex);
                        throw error;
                    }
                }
            }
        }
Beispiel #6
0
        // -------------------------------------------------------------------------
        #endregion

        #region Obtaining Multiple Result Sets Using a Data Reader
        // ------------------------ Obtaining Multiple Result Sets Using a Data Reader -------------------------
        // Data reader objects can obtain multiple result sets using a single command object.
        //
        public static void TestMultipleResultSetsUsingDataReaders()
        {
            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString =
                    @"Server=localhost\SQLEXPRESS;
                      Database=AutoLot;
                      Trusted_Connection=True;".Replace(" ", "").Replace(NewLine, "");

                connection.Open();

                Console.WriteLine(DbUtilities.GetInfo(connection));

                string sql = "SELECT * FROM Inventory;SELECT * FROM Customers";

                SqlCommand command = new SqlCommand(sql, connection);

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    do
                    {
                        Console.WriteLine("---------------- Table ----------------");
                        while (reader.Read())
                        {
                            Console.WriteLine("------------ Record ------------");
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                Console.WriteLine(reader.GetName(i) + " = " + reader.GetValue(i));
                            }
                            Console.WriteLine();
                        }
                        Console.WriteLine("--------------------------------");
                    } while (reader.NextResult());
                }
            }
        }
Beispiel #7
0
        // ------------------------ The Types of the System.Data Namespace -------------------------
        // This namespace contains types that are shared among all ADO.NET data providers, regardless of the underlying
        // data store.
        //
        // In addition to a number of database-centric exceptions (e.g., NoNullAllowedException,
        // RowNotInTableException, and MissingPrimaryKeyException),
        //
        // System.Data contains types that represent various database primitives (e.g., tables, rows,
        // columns, and constraints), as well as the common interfaces implemented by data provider objects.
        //
        // Table 21-4. Core Members of the System.Data Namespace
        // -----------------------------------------------------------------------------
        // Type             Meaning in Life
        // -----------------------------------------------------------------------------
        //
        // Constraint           Represents a constraint for a given DataColumn object
        //
        // DataColumn           Represents a single column within a DataTable object
        //
        // DataRow              Represents a single row within a DataTable object
        //
        // DataRelation         Represents a parent-child relationship between two DataTable objects
        //
        // DataSet              Represents an in-memory cache of data consisting of any number
        //                      of interrelated DataTable objects
        //
        // DataTable            Represents a tabular block of in-memory data
        //
        // DataTableReader      Allows you to treat a DataTable as a fire-hose cursor
        //                      (forward only, read-only data access)
        //
        // DataView             Represents a customized view of a DataTable for sorting, filtering,
        //                      searching, editing, and navigation
        //
        //
        // IDbCommand           Defines the core behavior of a command object
        //
        //
        // IDbConnection        Defines the core behavior of a connection object
        //
        // IDbTransaction       Defines the core behavior of a transaction object
        //
        // IDbCommand           Defines the core behavior of a connection object
        //
        // IDataParameter
        // IDbDataParameter     Defines the core behavior of a parameter object
        //
        // IDataAdapter         Defines the core behavior of a data adapter object
        // IDbDataAdapter       Extends IDataAdapter to provide additional functionality of
        //                      a data adapter object
        //
        // IDataRecord          defines many members that allow you to extract a
        //                      strongly typed value from the stream
        //
        // IDataReader          Extends IDataRecord, and Defines the core behavior
        //                      of a data reader object
        //
        // ----------------------------------------------------------------------------------------------
        //
        // Key System.Data Interfaces:
        // -----------------------------------------------------------------------
        //  public interface IDbConnection : IDisposable
        //  {
        //      string ConnectionString { get; set; }
        //      int ConnectionTimeout { get; }
        //      string Database { get; }
        //      ConnectionState State { get; }
        //      IDbTransaction BeginTransaction();
        //      IDbTransaction BeginTransaction(IsolationLevel il);
        //      void ChangeDatabase(string databaseName);
        //      void Close();
        //      IDbCommand CreateCommand();
        //      void Open();
        //  }

        //  public interface IDbTransaction : IDisposable
        //  {
        //      IDbConnection Connection { get; }
        //      IsolationLevel IsolationLevel { get; }
        //      void Commit();
        //      void Rollback();
        //  }


        //  public interface IDbCommand : IDisposable
        //  {
        //      IDbConnection Connection { get; set; }
        //      IDbTransaction Transaction { get; set; }
        //      string CommandText { get; set; }
        //      int CommandTimeout { get; set; }
        //      CommandType CommandType { get; set; }
        //      IDataParameterCollection Parameters { get; }
        //      UpdateRowSource UpdatedRowSource { get; set; }
        //      void Prepare();
        //      void Cancel();
        //      IDbDataParameter CreateParameter();
        //      int ExecuteNonQuery();
        //      IDataReader ExecuteReader();
        //      IDataReader ExecuteReader(CommandBehavior behavior);
        //      object ExecuteScalar();
        //  }

        //  public interface IDbDataParameter : IDataParameter
        //  {
        //      byte Precision { get; set; }
        //      byte Scale { get; set; }
        //      int Size { get; set; }
        //  }

        //  public interface IDataParameter
        //  {
        //      DbType DbType { get; set; }
        //      ParameterDirection Direction { get; set; }
        //      bool IsNullable { get; }
        //      string ParameterName { get; set; }
        //      string SourceColumn { get; set; }
        //      DataRowVersion SourceVersion { get; set; }
        //      object Value { get; set; }
        //  }


        //  public interface IDbDataAdapter : IDataAdapter
        //  {
        //      IDbCommand SelectCommand { get; set; }
        //      IDbCommand InsertCommand { get; set; }
        //      IDbCommand UpdateCommand { get; set; }
        //      IDbCommand DeleteCommand { get; set; }
        //  }

        //  public interface IDataAdapter
        //  {
        //      MissingMappingAction MissingMappingAction { get; set; }
        //      MissingSchemaAction MissingSchemaAction { get; set; }
        //      ITableMappingCollection TableMappings { get; }
        //      DataTable[] FillSchema(DataSet dataSet, SchemaType schemaType);
        //      int Fill(DataSet dataSet);
        //      IDataParameter[] GetFillParameters();
        //      int Update(DataSet dataSet);
        //  }


        //  public interface IDataReader : IDisposable, IDataRecord
        //  {
        //      int Depth { get; }
        //      bool IsClosed { get; }
        //      int RecordsAffected { get; }
        //      void Close();
        //      DataTable GetSchemaTable();
        //      bool NextResult();
        //      bool Read();
        //  }

        //  public interface IDataRecord
        //  {
        //      int FieldCount { get; }
        //      object this[int i] { get; }
        //      object this[string name] { get; }
        //      string GetName(int i);
        //      string GetDataTypeName(int i);
        //      Type GetFieldType(int i);
        //      object GetValue(int i);
        //      int GetValues(object[] values);
        //      int GetOrdinal(string name);
        //      bool GetBoolean(int i);
        //      byte GetByte(int i);
        //      long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length);
        //      char GetChar(int i);
        //      long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length);
        //      Guid GetGuid(int i);
        //      short GetInt16(int i);
        //      int GetInt32(int i);
        //      long GetInt64(int i);
        //      float GetFloat(int i);
        //      double GetDouble(int i);
        //      string GetString(int i);
        //      Decimal GetDecimal(int i);
        //      DateTime GetDateTime(int i);
        //      IDataReader GetData(int i);
        //      bool IsDBNull(int i);
        //  }

        // -------------------------------------------------------------------------
        #endregion

        #region Create a Simple Data Provider Factory
        // ------------------------ Create a Simple Data Provider Factory -------------------------
        // Create a Simple Factory Model
        // -----------------------------
        // The.NET data provider factory pattern allows you to build a single codebase using generalized data access
        // types.Furthermore, using application configuration files(and the <connectionStrings> subelement), you
        // can obtain providers and connection strings declaratively, without the need to recompile or redeploy the
        // assembly that uses the ADO.NET APIs.
        public static void TestSimpleDataProviderFactory()
        {
            Console.WriteLine("---------------- Very Simple Connection Factory ----------------");

            try
            {
                DataProviderFactory factory = new DataProviderFactory();


                IDbConnection connection = factory.GetConnection(ConfigurationManager.AppSettings["provider"]);

                connection.ConnectionString = ConfigurationManager.ConnectionStrings["master"].ConnectionString;

                Console.WriteLine(DbUtilities.GetInfo(connection));

                using (connection)
                {
                    connection.Open();

                    // Make your Ado.Net Operation here!
                    //...

                    Console.WriteLine(DbUtilities.GetInfo(connection));
                }
                Console.WriteLine(DbUtilities.GetInfo(connection));
            }
            catch (Exception exp)
            {
                Console.WriteLine();
                Console.WriteLine("Main Exception");
                Console.WriteLine("---------------------------");
                Console.WriteLine("Message:{0}", exp.Message);
                Console.WriteLine("StackTrace:{0}", exp.StackTrace);
                Console.WriteLine();
            }
        }
Beispiel #8
0
        // -------------------------------------------------------------------------
        #endregion

        #region The ADO.NET Data Provider Factory Model
        // ------------------------ The ADO.NET Data Provider Factory Model -------------------------
        //To understand the data provider factory implementation, recall from Table 21-1 that the classes within a
        //data provider all derive from the same base classes defined within the System.Data.Common namespace.

        //•	 DbCommand: The abstract base class for all command classes

        //•	 DbConnection: The abstract base class for all connection classes

        //•	 DbDataAdapter: The abstract base class for all data adapter classes

        //•	 DbDataRecorder: The abstract base class for all data recorder classes

        //•	 DbDataReader: The abstract base class for all data reader classes

        //•	 DbParameter: The abstract base class for all parameter classes

        //•	 DbParameterCollection: The abstract base class for all parameter collection classes

        //•	 DbTransaction: The abstract base class for all transaction classes


        // with the already made Provider Factory in System.Data.Common:
        // ---------------------------------------------------------------------------
        // DbProviderFactory: The abstract
        // ------------------
        //      Represents a set of methods for creating instances of a provider's
        //      implementation of the data source classes.

        // DbProviderFactories:
        // --------------------
        // Represents a set of static methods for creating one or more instances
        // of System.Data.Common.DbProviderFactory classes.

        public static void TestDataProviderFactoryModel()
        {
            DbProviderFactory factory =
                DbProviderFactories.GetFactory(ConfigurationManager.AppSettings["provider"]);

            using (DbConnection connection = factory.CreateConnection())
            {
                if (connection == null)
                {
                    Console.WriteLine("NUll Connection!");
                    return;
                }

                // Connection String
                connection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["master"].ConnectionString;

                // Open Connection
                connection.Open();

                // print connection information to console
                Console.WriteLine(DbUtilities.GetInfo(connection));

                // Change Database from master to another one (AutoLot)
                connection.ChangeDatabase("AutoLot");

                Console.WriteLine(DbUtilities.GetInfo(connection));


                DbCommand command = factory.CreateCommand();

                // or Get from DbConnection object
                //DbCommand command2 = connection.CreateCommand();

                // if you get command from factory you should manualy assign connection object
                command.Connection = connection;

                command.CommandText = "SELECT * FROM Inventory";

                Console.WriteLine(DbUtilities.GetInfo(command));

                // Execute Command ...
                using (DbDataReader reader = command.ExecuteReader())
                {
                    Console.WriteLine(DbUtilities.GetInfo(reader));

                    Console.WriteLine("------------------ Inventory ---------------------");
                    Console.WriteLine("     CarID       PetName");
                    Console.WriteLine("----------------------------------------");
                    while (reader.Read())
                    {
                        Console.WriteLine($"     {reader["CarID"]}       {reader["PetName"]}");
                    }
                }


                // so if you need to a specific provider feature you can cast to it
                // but code will be harder to maintain and will be less flexable:

                //SqlConnection sqlServerConnection = connection as SqlConnection;

                // call specific SQLServer Connection Method:

                //Console.WriteLine(sqlServerConnection.ServerVersion);
            }
        }
Beispiel #9
0
        // -------------------------------------------------------------------------
        #endregion

        #region Understanding Database Transactions
        // ------------------------ Understanding Database Transactions -------------------------
        // a transaction is a set of database operations that must either all succeed or all fail as a collective unit.
        //
        // transactions are quite important to ensure that table data is safe, valid, and consistent.
        // Transactions are important when a database operation involves interacting with multiple tables or multiple
        // stored procedures(or a combination of database atoms).
        //
        // the DBMS ensures that all related steps occur as a single unit.
        // If any part of the transaction fails, the entire operation is rolled back to the original
        // state.On the other hand, if all steps succeed, the transaction is committed.

        // NOTE:
        // ------------------------------------------------------------------------------------------------------------------
        // Note You might be familiar with the acronym ACiD from looking at transactional literature.This represents
        // the four key properties of a prim-and-proper transaction: atomic (all or nothing), consistent(data remains stable
        // throughout the transaction), isolated(transactions do not step on each other’s feet), and durable(transactions
        // are saved and logged).
        // ------------------------------------------------------------------------------------------------------------------
        //
        //  public interface IDbTransaction : IDisposable
        //  {
        //      IDbConnection Connection { get; }
        //      IsolationLevel IsolationLevel { get; }
        //      void Commit();
        //      void Rollback();
        //  }
        //
        //
        // You call
        // the Commit() method when each of your database operations have succeeded.Doing this causes each of
        // the pending changes to be persisted in the data store.Conversely, you can call the Rollback() method in
        // the event of a runtime exception, which informs the DMBS to disregard any pending changes, leaving the
        // original data intact.
        //
        //
        //
        public static void ProcessCreditRisk(bool throwEx, int custId)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString =
                    @"Server=localhost\SQLEXPRESS;
                      Database=AutoLot;
                      Trusted_Connection=True;".Replace(" ", "").Replace(NewLine, "");

                connection.Open();

                Console.WriteLine(DbUtilities.GetInfo(connection));

                string fName, lName;

                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.Text;
                    command.CommandText = $"SELECT * From Customers where CustId={custId}";

                    using (var dataReader = command.ExecuteReader())
                    {
                        if (dataReader.HasRows)
                        {
                            dataReader.Read();
                            fName = (string)dataReader["FirstName"];
                            lName = (string)dataReader["LastName"];
                        }
                        else
                        {
                            connection.Close();
                            return;
                        }
                    }
                }

                SqlTransaction transaction = null;
                try
                {
                    transaction = connection.BeginTransaction();

                    using (SqlCommand deleteCommand = new SqlCommand())
                        using (SqlCommand insertCommand = new SqlCommand())
                        {
                            deleteCommand.Connection = connection;
                            insertCommand.Connection = connection;

                            deleteCommand.CommandType = CommandType.Text;
                            insertCommand.CommandType = CommandType.Text;

                            deleteCommand.CommandText = $"DELETE FROM Customers WHERE CustId = {custId}";
                            insertCommand.CommandText = $"INSERT INTO CreditRisks(FirstName ,LastName) VALUES ('{fName}', '{lName}')";

                            deleteCommand.Transaction = transaction;
                            insertCommand.Transaction = transaction;

                            deleteCommand.ExecuteNonQuery();
                            insertCommand.ExecuteNonQuery();

                            if (throwEx)
                            {
                                throw new Exception("Sorry! Database error! Tx failed...");
                            }

                            transaction.Commit();

                            Console.WriteLine("Transaction done Successfully!");
                        }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.StackTrace);
                    Console.WriteLine();
                    Console.WriteLine("Rollback:" + ex.Message);
                    // Any error will roll back transaction. Using the new conditional access operator to
                    // check for null.
                    transaction?.Rollback();
                }
            }
        }