public AjaxResult getColumns(string table, string db = "", string uid = "", string pwd = "", string ds = "")
{
string _connectionString = string.Format(@"Data Source={0};Initial Catalog={3};Persist Security Info=True;User ID={1};Password={2};Pooling=true;Min Pool Size=1;Max Pool Size=500;MultipleActiveResultSets=true"
, string.IsNullOrEmpty(ds) ? "" : ds
, string.IsNullOrEmpty(uid) ? "" : uid
, string.IsNullOrEmpty(pwd) ? "" : pwd
, string.IsNullOrEmpty(db) ? "" : db
);
SqlServerDAL dal = string.IsNullOrEmpty(ds) ? new SqlServerDAL() : new SqlServerDAL(_connectionString);
string sql = string.Format(@"
select obj.name table_name,col.name column_name
,t.name data_type,col.length data_length,col.isnullable,ep.[value] column_description
,isnull(pkIndex.is_primary_key,cast(0 as bit)) is_primary_key
from syscolumns col
inner join sysobjects obj on col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0
left join systypes t on col.xusertype = t.xusertype
left join sys.extended_properties ep on col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description'
left join sys.index_columns colIndex on colIndex.object_id=col.id and colIndex.column_id=col.colid
left join sys.indexes pkIndex on pkIndex.object_id = colIndex.object_id and pkIndex.index_id = colIndex.index_id
where obj.name='{0}'
", string.IsNullOrEmpty(table) ? "" : table);
return(new AjaxResult
{
statusCode = 200,
data = dal.ExecuteDataTable(sql, null, CommandType.Text)
});
}
public DataTable SelectByCriteria(string strCriteria)
{
DataSet ds = null;
SqlParameter[] parms =
{
new SqlParameter("pCriteria", strCriteria),
};
ds = SqlServerDAL.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, "sp_UserMenuSelectByCriteria", parms);
return(ds.Tables[0]);
}
private void cbDepart_SelectedIndexChanged(object sender, EventArgs e)
{
int index = cbDepart.SelectedIndex;
SqlServerDAL sql = new SqlServerDAL();
List <Employee_info> list = sql.Rename(index);
lbemployee.DisplayMember = "EmployeeName";
lbemployee.ValueMember = "EmployeeId";
lbemployee.DataSource = list;
}
public void Delete(int sintUserid, ref SqlTransaction trans)
{
SqlParameter[] parms =
{
new SqlParameter("pUserID", SqlDbType.SmallInt, 2, ParameterDirection.Input, false, 5, 0, "UserID", DataRowVersion.Current, sintUserid)
};
SqlServerDAL.ExecuteNonQuery(trans,
CommandType.StoredProcedure,
"sp_UserMenuDelete",
parms);
}
private void cmbDept_SelectedIndexChanged(object sender, EventArgs e)
{
//lbname.Items.Clear();
int index = cmbDept.SelectedIndex;
SqlServerDAL sql = new SqlServerDAL();
//SqlDataReader dt= sql.Rename(index);
List <Employee_info> list = sql.Rename(index);
lbname.DataSource = list;
lbname.DisplayMember = "EmployeeName";
lbname.ValueMember = "EmployeeId";
}
public void insert_table_into_db(DataTable dt, int startDepth, int endDepth, string jh)
{ //把dt的一部分放入数据库里
DataTable insert_dt = this.create_new_dt_by_depth(dt, startDepth, endDepth);
DataColumn jh_column = new DataColumn("jh");
insert_dt.Columns.Add(jh_column);
foreach (DataRow row in insert_dt.Rows)
{
row["jh"] = jh;
}
SqlServerDAL.SqlBulkCopyColumnMapping("P_TABLE", insert_dt);
}
public AjaxResult getDatabase(string uid = "", string pwd = "", string ds = "")
{
string _connectionString = string.Format(@"Data Source={0};Initial Catalog=master;Persist Security Info=True;User ID={1};Password={2};Pooling=true;Min Pool Size=1;Max Pool Size=500;MultipleActiveResultSets=true"
, string.IsNullOrEmpty(ds) ? "" : ds
, string.IsNullOrEmpty(uid) ? "" : uid
, string.IsNullOrEmpty(pwd) ? "" : pwd
);
SqlServerDAL dal = string.IsNullOrEmpty(ds) ? new SqlServerDAL(): new SqlServerDAL(_connectionString);
return(new AjaxResult
{
statusCode = 200,
data = dal.ExecuteDataTable("SELECT name,filename FROM SYSDATABASES ORDER BY name", null, CommandType.Text)
});
}
public AjaxResult getTable(string db = "", string uid = "", string pwd = "", string ds = "")
{
string _connectionString = string.Format(@"Data Source={0};Initial Catalog={3};Persist Security Info=True;User ID={1};Password={2};Pooling=true;Min Pool Size=1;Max Pool Size=500;MultipleActiveResultSets=true"
, string.IsNullOrEmpty(ds) ? "" : ds
, string.IsNullOrEmpty(uid) ? "" : uid
, string.IsNullOrEmpty(pwd) ? "" : pwd
, string.IsNullOrEmpty(db) ? "" : db
);
SqlServerDAL dal = string.IsNullOrEmpty(ds) ? new SqlServerDAL() : new SqlServerDAL(_connectionString);
return(new AjaxResult
{
statusCode = 200,
data = dal.ExecuteDataTable("select name,crdate from dbo.sysobjects where xtype='U ' AND [status] >= 0", null, CommandType.Text)
});
}
public DataTable SelectByID(int sintUserid, int sintMenuid)
{
DataSet ds = null;
SqlParameter[] parms =
{
new SqlParameter("pUserID", SqlDbType.SmallInt, 2, ParameterDirection.Input, false, 5, 0, "UserID", DataRowVersion.Current, sintUserid),
new SqlParameter("pMenuID", SqlDbType.SmallInt, 2, ParameterDirection.Input, false, 5, 0, "MenuID", DataRowVersion.Current, sintMenuid),
};
ds = SqlServerDAL.ExecuteDataset(strConnectionString,
CommandType.StoredProcedure,
"sp_UserMenuSelectByID",
parms);
return(ds.Tables[0]);
}
public void Update(int sintUserid, int sintMenuid, char strCanview, char strCaninsert, char strCanedit, char strCandelete, char strCanapprove, char strCanextra, string strCreatedby, ref SqlTransaction trans)
{
SqlParameter[] parms =
{
new SqlParameter("pUserID", SqlDbType.SmallInt, 2, ParameterDirection.Input, false, 5, 0, "UserID", DataRowVersion.Current, sintUserid),
new SqlParameter("pMenuID", SqlDbType.SmallInt, 2, ParameterDirection.Input, false, 5, 0, "MenuID", DataRowVersion.Current, sintMenuid),
new SqlParameter("pCanView", SqlDbType.VarChar, 1, ParameterDirection.Input, false, 0, 0, "CanView", DataRowVersion.Current, strCanview),
new SqlParameter("pCanInsert", SqlDbType.VarChar, 1, ParameterDirection.Input, false, 0, 0, "CanInsert", DataRowVersion.Current, strCaninsert),
new SqlParameter("pCanEdit", SqlDbType.VarChar, 1, ParameterDirection.Input, false, 0, 0, "CanEdit", DataRowVersion.Current, strCanedit),
new SqlParameter("pCanDelete", SqlDbType.VarChar, 1, ParameterDirection.Input, false, 0, 0, "CanDelete", DataRowVersion.Current, strCandelete),
new SqlParameter("pCanApprove", SqlDbType.VarChar, 1, ParameterDirection.Input, true, 0, 0, "CanApprove", DataRowVersion.Current, strCanapprove),
new SqlParameter("pCanExtra", SqlDbType.VarChar, 1, ParameterDirection.Input, false, 0, 0, "CanExtra", DataRowVersion.Current, strCanextra),
new SqlParameter("pUpdatedBy", SqlDbType.NVarChar, 51, ParameterDirection.Input, true, 0, 0, "CreatedBy", DataRowVersion.Current, strCreatedby)
};
SqlServerDAL.ExecuteNonQuery(trans,
CommandType.StoredProcedure,
"sp_UserMenuUpdate",
parms);
}
public void change(DataTable dt)
{
string sql = string.Format("use DQLREPORTDB select * from P_TABLE where jh='{0}' order by DEPTH asc", FrameDesign.JH);
DataTable newdt = SqlServerDAL.gettable(sql);
newdt.Columns.Remove("jh");
List <DataTable> dts = this.split_dt_by_DEPTH(newdt);
for (int i = 0; i < dts.Count - 1; i++)
{
string up = dts[i].Rows[dts[i].Rows.Count - 1]["depth"].ToString();
string down = dts[i + 1].Rows[0]["depth"].ToString();
if (up == down)
{
dts[i + 1].Rows.RemoveAt(0);
}
}
foreach (DataTable item in dts)
{
List <int> index = this.find_depth_index(dt, item);
for (int i = index[0], j = 0; i <= index[1]; i++, j++)
{
try
{
for (int k = 0; k < dt.Columns.Count; k++)
{
dt.Rows[i][k] = item.Rows[j][k];
}
}
catch
{
break;
}
}
}
//
int kk = 9;
}
public void delete_ptable(int depthmin, int depthmax)
{
string sqlstr = string.Format("USE DQLREPORTDB delete from P_TABLE where depth>={0} and depth<={1} and jh='{2}' ", depthmin, depthmax, FrameDesign.JH);
int i = SqlServerDAL.ExecuteNonQueryy(sqlstr);
}
public SqlServerDALTests()
{
SqlServerDAL.connectionString = "Data Source=127.0.0.1;Initial Catalog=RTPay;Persist Security Info=True;User ID=sa;Password=123456";
_dal = new SqlServerDAL();
}
/// <summary>
/// 构造方法
/// </summary>
public SqlServerBLL()
{
_dal = new SqlServerDAL();
}
