PDA

View Full Version : سوال: ایجاد stored procedure برای اضافه کردن فیلدها از فرم asp.net در یک جدول



davood.hanifi
شنبه 25 شهریور 1391, 21:40 عصر
با یسلام بنده میخوام واسه جدول زیر یک stored procedure برای اضافه کردن فیلد بنویسم ولی مبتدی هستم خواهشمندم کمک کنید.جدول رو هم ضمیمه کردم.

meisam12
شنبه 25 شهریور 1391, 22:00 عصر
سلام
فرضاً می خواهید به جدول کاربران اطلاعات اضافه کنید به شکل زیر عمل کنید.
یک کلاس به نام DBobject.cs بساز و این کدها را داخلش بنویس

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace NovinMedia.Data
{
/// <summary>
/// DbObject is the class from which all classes in the Data Services
/// Tier inherit. The core functionality of establishing a connection
/// with the database and executing simple stored procedures is also
/// provided by this base class.
/// </summary>
public class DbObject
{
protected SqlConnection Connection;
private string connectionString;

/// <summary>
/// A parameterized constructor, it allows us to take a connection
/// string as a constructor argument, automatically instantiating
/// a new connection.
/// </summary>
/// <param name="newConnectionString">Connection String to the associated database</param>
public DbObject( string newConnectionString )
{
connectionString = newConnectionString;
Connection = new SqlConnection( connectionString );
}
public DbObject()
{
connectionString = ConfigurationManager.ConnectionStrings["eShop_13"].ToString();
Connection = new SqlConnection(connectionString);
}

/// <summary>
/// Protected property that exposes the connection string
/// to inheriting classes. Read-Only.
/// </summary>
protected string ConnectionString
{
get
{
return connectionString;
}
}

/// <summary>
/// Private routine allowed only by this base class, it automates the task
/// of building a SqlCommand object designed to obtain a return value from
/// the stored procedure.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure in the DB, eg. sp_DoTask</param>
/// <param name="parameters">Array of IDataParameter objects containing parameters to the stored proc</param>
/// <returns>Newly instantiated SqlCommand instance</returns>
private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand( storedProcName, parameters );

command.Parameters.Add( new SqlParameter ( "ReturnValue",
SqlDbType.Int,
4, /* Size */
ParameterDirection.ReturnValue,
false, /* is nullable */
0, /* byte precision */
0, /* byte scale */
string.Empty,
DataRowVersion.Default,
null ));

return command;
}


/// <summary>
/// Builds a SqlCommand designed to return a SqlDataReader, and not
/// an actual integer value.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="parameters">Array of IDataParameter objects</param>
/// <returns></returns>
private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName, Connection );
command.CommandType = CommandType.StoredProcedure;

foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter );
}

return command;

}

/// <summary>
/// Runs a stored procedure, can only be called by those classes deriving
/// from this base. It returns an integer indicating the return value of the
/// stored procedure, and also returns the value of the RowsAffected aspect
/// of the stored procedure that is returned by the ExecuteNonQuery method.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="parameters">Array of IDataParameter objects</param>
/// <param name="rowsAffected">Number of rows affected by the stored procedure.</param>
/// <returns>An integer indicating return value of the stored procedure</returns>
public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
{
int result;

Connection.Open();
SqlCommand command = BuildIntCommand( storedProcName, parameters );
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
Connection.Close();
return result;
}

/// <summary>
/// Will run a stored procedure, can only be called by those classes deriving
/// from this base. It returns a SqlDataReader containing the result of the stored
/// procedure.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="parameters">Array of parameters to be passed to the procedure</param>
/// <returns>A newly instantiated SqlDataReader object</returns>
public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
SqlDataReader returnReader;

Connection.Open();
SqlCommand command = BuildQueryCommand( storedProcName, parameters );
command.CommandType = CommandType.StoredProcedure;

returnReader = command.ExecuteReader();
//Connection.Close();
return returnReader;
}

/// <summary>
/// Creates a DataSet by running the stored procedure and placing the results
/// of the query/proc into the given tablename.
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
DataSet dataSet = new DataSet();
Connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
Connection.Close();

return dataSet;
}

/// <summary>
/// Takes an -existing- dataset and fills the given table name with the results
/// of the stored procedure.
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <param name="dataSet"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName)
{
Connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildIntCommand( storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
Connection.Close();
}

/// <summary>
/// Creates a DataSet by running the query and placing the results
/// of the query/proc into the given tablename.
/// </summary>
/// <param name="Query"></param>
/// <param name="tableName"></param>
/// <returns>DataSet</returns>
public DataSet RunQuery(string Query, string tableName)
{
DataSet dataSet = new DataSet();
Connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();

SqlCommand cmd = new SqlCommand(Query,Connection);
cmd.CommandType = CommandType.Text;
sqlDA.SelectCommand = cmd;
sqlDA.Fill(dataSet, tableName);
Connection.Close();

return dataSet;
}


}
}


و بعد هرجا که خواستی اضافه کنی کد زیر را اضافه کن.

public static int InsertRow(string Name,string Family,string UserName,string HashPassword,string SaltPassword,string Email,string Address,string Tel,string Mobile,DateTime Age,bool Sex,DateTime registerDate,string imageURL,int role_id,bool Dismissal,bool Apprroved,string thumbUrlImage)
{
int RowsAffected = 0;
int Result = 0;
DbObject dbo = new DbObject();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("Name",Name),
new SqlParameter("Family",Family),
new SqlParameter("UserName",UserName),
new SqlParameter("HashPassword",HashPassword),
new SqlParameter("SaltPassword",SaltPassword),
new SqlParameter("Email",Email),
new SqlParameter("Address",Address),
new SqlParameter("Tel",Tel),
new SqlParameter("Mobile",Mobile),
new SqlParameter("Age",Age),
new SqlParameter("Sex",Sex),
new SqlParameter("registerDate",registerDate),
new SqlParameter("imageURL",imageURL),
new SqlParameter("role_id",role_id),
new SqlParameter("Dismissal",Dismissal),
new SqlParameter("Apprroved",Apprroved),
new SqlParameter("thumbUrlImage",thumbUrlImage)
};
Result = dbo.RunProcedure("Users_Insert", parameters, out RowsAffected);
return Result;
}